Data Connector is a Google Sheets Add-on that lets you import (and export) data to/from Google Sheets

Overview

Data Connector

Data Connector is a Google Sheets Add-on that lets you import (and export) data to/from Google Sheets. Our roadmap:

  1. Connect to JSON/XML/CSV APIs
  2. Import data from any website, including those with JavaScript via a headless browser
  3. JMESPath/XPath/JSONPath/Pup/JQ and other filters
  4. Connect to PostgreSQL, MySQL, and other databases
  5. Run your commands in Google Sheets/Excel/Slack/Airtable

Q: Why create this when there's like a bazillion Add-ons that do the same thing?

A: Privacy! Add-ons in Google Sheets have a lot of power to read your Sheet. Further, connecting to various APIs and databases means we may need to store your API Keys and other information securely (unless, of course, you parameterize your queries). We want to be transparent about our methods and are always open to suggestions on how to improve the code. While other Add-ons might claim to be privacy-centric, you can never be assured until you see the code. Further, no other Add-on fulfilled our wishlist above.

More about how we stack up to our competitors can be found on our website

🏃 How do I run this?

The easiest way to run the Data Connector Add-on is to install it from the Google Workspace Marketplace: https://workspace.google.com/marketplace/app/appname/529655450076

🔨 If, instead, you want to run your own version you will need to setup both the backend API and the Google Sheets parts.

  • Clone this repo:

    git clone https://github.com/brentadamson/dataconnector.git

Backend

  • Set your environment variables:

    IMPORTANT: The JWT_SECRET MUST be the same as the JWT_SECRET environment variable set below in the Google Sheets section.

    export DATACONNECTOR_POSTGRESQL_USER=user
    export DATACONNECTOR_POSTGRESQL_PASSWORD=mypassword
    export DATACONNECTOR_POSTGRESQL_HOST=localhost
    export DATACONNECTOR_POSTGRESQL_DATABASE=mydatabase
    export DATACONNECTOR_POSTGRESQL_PORT=5432
    export DATACONNECTOR_ENCRYPTION_KEY=my-32-bit-hex-encoded-encryption-key
    export DATACONNECTOR_JWT_SECRET=secret
    
  • cd backend/backend/cmd

  • go run .

    Your backend service will be listening at http://127.0.0.1:8000 by default

Google Sheets

  • From the main dataconnector directory:

    cd googlesheets

    npm install

  • Login to clasp, which lets you manage Apps scripts from the commandline:

    npm run login

  • Setup a new Sheet and script by running:

    npm run setup

    If you already have an existing Sheet and script:

    npm run setup:use-id

  • In your new Sheets script, create a new file called env.gs and paste the following code:

    function updateEnvVariables(){
      PropertiesService.getScriptProperties().setProperty('DOMAIN', 'https://api.example.com');
      PropertiesService.getScriptProperties().setProperty('JWT_SECRET', 'secret');
    }
    

    Update the DOMAIN and JWT_SECRET, select the updateEnvVariables in the functions dropdown list and hit the play button.

    IMPORTANT: The JWT_SECRET MUST be the same as the JWT_SECRET environment variable set above in the Backend section.

    Once that's complete, you can delete the env.gs file

  • To make changes to the Add-on, enable hot reloading:

    mkcert -install

    npm run setup:https

    npm run start

    Make some changes in the code and see the changes instantly in the Add-on.

    Note: changes to Code.js will require a restart.

  • To deploy:

    npm run deploy

To contribute

Before you do anything, please open up an issue or assign yourself an existing one. This helps coordinate things.

Issues
  • Documentation button overlaps the cancel and save buttons on Brave browser

    Documentation button overlaps the cancel and save buttons on Brave browser

    See https://github.com/brentadamson/dataconnector/issues/36

    https://imgur.com/a/DBxsARI

    Unfortunately, I'm not able to replicate this in Brave for Ubuntu but will accept a pull request that fixes it.

    opened by brentadamson 5
  • data connector error: unhandled params

    data connector error: unhandled params

    Hi,

    Out of the blue this error began appearing without changing anything to my configs.

    Also, I cannot click "cancel" or "save" when editing an API connector due to the "Documentation" button overlapping "Cancel" and "Save."

    Thanks!

    opened by jclaudino 5
  • Unknown function

    Unknown function "API"

    Hi, I've been using this plugin for a few months now and it's been fantastic, thank you! However, this latest update has resulted in my sheet no longer recognizing the "API" function, and my saved queries have all disappeared.

    Did the function name change to something new? I've tried re-installing the plugin to no avail. As someone else mentioned, some updated documentation would be much appreciated as well.

    opened by jclaudino 5
  • Data Connector Add-on Permissions

    Data Connector Add-on Permissions

    The Add-on requests full permission on all google drive sheets. while it should only request for permission on the sheets where the addon is installed into. as per my knowledge this can be done by adding the following lines to the appsscript.json in google apps scripts.

    "oauthScopes": [ "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/spreadsheets.currentonly" ],

    opened by nusaif 4
  • I opened it in google sheets and it got an error

    I opened it in google sheets and it got an error

    react-dom.production.min.js:125 Error: Minified React error #31; visit https://reactjs.org/docs/error-decoder.html?invariant=31&args[]=ScriptError%3A%20Authorization%20is%20required%20to%20perform%20that%20action.&args[]= for the full message or use the non-minified dev environment for full errors and additional helpful warnings.
        at Nc (react-dom.production.min.js:72)
        at react-dom.production.min.js:81
        at T (react-dom.production.min.js:95)
        at zj (react-dom.production.min.js:230)
        at Th (react-dom.production.min.js:152)
        at tj (react-dom.production.min.js:152)
        at Te (react-dom.production.min.js:146)
        at react-dom.production.min.js:61
        at unstable_runWithPriority (react.production.min.js:25)
        at Da (react-dom.production.min.js:60)
    Me @ react-dom.production.min.js:125
    react-dom.production.min.js:72 Uncaught (in promise) Error: Minified React error #31; visit https://reactjs.org/docs/error-decoder.html?invariant=31&args[]=ScriptError%3A%20Authorization%20is%20required%20to%20perform%20that%20action.&args[]= for the full message or use the non-minified dev environment for full errors and additional helpful warnings.
        at Nc (react-dom.production.min.js:72)
        at react-dom.production.min.js:81
        at T (react-dom.production.min.js:95)
        at zj (react-dom.production.min.js:230)
        at Th (react-dom.production.min.js:152)
        at tj (react-dom.production.min.js:152)
        at Te (react-dom.production.min.js:146)
        at react-dom.production.min.js:61
        at unstable_runWithPriority (react.production.min.js:25)
        at Da (react-dom.production.min.js:60)
        
        ```
    opened by amozoss 3
  • Google My Business - Quota exceeded

    Google My Business - Quota exceeded

    Just setup Google My Business OAuth2 and doing a simple GET request to https://mybusinessaccountmanagement.googleapis.com/v1/accounts results in an error: Quota exceeded for quota metric 'Requests per minute' of service 'mybusinessaccountmanagement.googleapis.com for consumer 'project_number:<number>'

    The prerequisites say to fill out a form, which I did. It might take a few days for them to review it then I'll try it again.

    opened by brentadamson 1
  • Simplify everything

    Simplify everything

    This simplifies everything and makes things more secure and private. Uses getUserProperties() to store user data rather than storing data in a database

    opened by brentadamson 0
  • Facebook Ads Oauth2 doesn't work

    Facebook Ads Oauth2 doesn't work

    Not really sure what I am missing on the Facebook Ads Oauth2 connection. I'm thinking either the scopes are wrong and/or my client isn't setup correctly.

    opened by brentadamson 0
  • Hyphen in the name of an object attribute return an error when trying to filter

    Hyphen in the name of an object attribute return an error when trying to filter

    Dataconnector is unable to filter correctly if there is an Hyphen in the object attribute name. Example: This call: https://api.coingecko.com/api/v3/simple/price?ids=band-protocol&vs_currencies=usd returns: {"band-protocol":{"usd":17.19}}

    If you don't filter, you get the return exactly as above. However, if you try "band-protocol" in the filter, in any way you get the error: data connector error: SyntaxError: Unexpected token at the end of the expression: tNumber: "{"band-protocol":{"usd":17.19}}

    opened by leonilsonlopes 2
  • Update the docs README.md

    Update the docs README.md

    https://github.com/brentadamson/dataconnector/tree/main/docs still points to Docusaurus. Should have instructions on how to run as well as how to add a new tutorial.

    opened by brentadamson 0
Owner
Brent Adamson
Brent Adamson
Simple, yet powerful Adcell go client to import data feeds into you projects.

adcell-go Simple, yet powerful Adcell go client to import data feeds into you projects. Explore the docs » View Demo · Report Bug · Request Feature Ta

Matthias Bruns 0 Oct 31, 2021
Provides the function Parallel to create a synchronous in memory pipe and lets you write to and read from the pipe parallelly

iopipe provides the function Parallel to create a synchronous in memory pipe and lets you write to and read from the pipe parallely

Ozgur Erdem 0 Jan 25, 2022
rconn is a multiplatform program for creating generic reverse connections. Lets you consume services that are behind firewall or NAT without opening ports or port-forwarding.

rconn (r[everse] conn[ection]) is a multiplatform program for creating reverse connections. It lets you consume services that are behind NAT and/or fi

Hikmat Jafarli 230 Jun 20, 2022
Proxy your Go Module`s Import Path from your own domain to a public host (e.g. github.com).

Go Modules Remote Import Path Proxy Proxy your Go Module`s Import Path from your own domain to a public host (e.g. github.com). For example Uber (buil

Stefan Kürzeder 2 Nov 2, 2021
gproxy is a tiny service/library for creating lets-encrypt/acme secured gRPC and http reverse proxies

gproxy is a reverse proxy service AND library for creating flexible, expression-based, lets-encrypt/acme secured gRPC/http reverse proxies GProxy as a

null 15 Mar 25, 2022
A Wireguard VPN Server Manager and API to add and remove clients

Wireguard Manager And API A manager and API to add, remove clients as well as other features such as an auto reapplier which deletes and adds back a c

null 124 Jun 15, 2022
Automatically register a list of domain names, add them to Cloudflare and set DNS records.

NameCannon Automatically register a list of domain names, add them as zones on Cloudflare, then add DNS records. Usage $ ./NameCannon --namesiloSecret

Kevin Roleke 1 Jan 26, 2022
ScriptTiger 14 Jun 26, 2022
A proxy to add `/federate` to Thanos monitoring

Thanos Federate Proxy A proxy to convert /federate queries to /v1/api/query and respond in open metrics format. The most common use case for this prox

Snapp Cab Incubators 13 May 24, 2022
Protocol Buffers - Google's data interchange format

Protocol Buffers - Google's data interchange format Copyright 2008 Google Inc. https://developers.google.com/protocol-buffers/ Overview Protocol Buffe

Protocol Buffers 55k Jun 21, 2022
Go support for Protocol Buffers - Google's data interchange format

Go support for Protocol Buffers - Google's data interchange format Google's data interchange format. Copyright 2010 The Go Authors. https://github.com

Tinker Board - Android 0 Dec 15, 2021
Assanlab - JSON input data with counts on how many times you showed an ad on each individual domain

JSON input data with counts on how many times you showed an ad on each individua

null 1 Dec 31, 2021
grobotstxt is a native Go port of Google's robots.txt parser and matcher library.

grobotstxt grobotstxt is a native Go port of Google's robots.txt parser and matcher C++ library. Direct function-for-function conversion/port Preserve

Jim Smart 86 Apr 7, 2022
Go-web-dev - Web Development With Google's Golang Programming Language Code Snippet and Exercises.

Web Development With Google's Golang Programming Language Code Snippet and Exercises This repository contains my code snippets, hands on exercises and

null 2 Jan 16, 2022
Creates a linux group of users synced to your Google Workspace users and automatically imports their public SSH keys.

Creates a linux group of users synced to your Google Workspace users and automatically imports their public SSH keys.

Dane Tuso 1 Jan 27, 2022
gh is GitHub on the command line. It brings pull requests, issues, and other GitHub concepts to the terminal next to where you are already working with git and your code

gh is GitHub on the command line. It brings pull requests, issues, and other GitHub concepts to the terminal next to where you are already working with git and your code

frenchy77 0 Jan 24, 2022
Handling Google Authenitcator codes.

gotp Handling Google Authenticator codes in your terminal Getting started Grab the binary The quickest way to get set up is to download the latest bin

Wesley Martin 12 Dec 8, 2021
Get related domains / subdomains by looking at Google Analytics IDs

AnalyticsRelationships This script try to get related domains / subdomains by looking at Google Analytics IDs from a URL. First search for ID of Googl

Josué Encinar 124 Jun 19, 2022
Access Google Ads API via GRPC

google-ads-pb You can use the golang library to interact with the Google Ads API across grpc. This library is not the official Google Ads API library.

null 9 Jun 10, 2022