Build Live Connection Between Google Sheets & ClicData

Google Sheets has become one of the most popular spreadsheet applications in the business world. Many businesses rely on its collaborative features for spur-of-the-moment project tracking or to build and maintain core company datasets that are critical to their operations.

Eventually, you reach a certain point where building and maintaining visualizations within your Google Sheet becomes unmanageable. As your either the size of your sheet grows or the number of people interacting with it grows it becomes prone to errors and slow-downs which directly affect your displays.

Moving the visualizations to an external tool is a great way to off-loading the performance burden from your sheet and ensure that no accidental edits or deletions of important calculations or displays occur. Though the move does come with a cost. Since the version of data used outside of your Google Sheets ecosystem is static, things start to fall out of sync.

What if you want your data to update in real-time, just as if the visualization was built in Google Sheets itself? One option is to set up a schedule within ClicData to pull your data every few minutes. However, setting your connection up like this can be inefficient, as the sheet may not be updated that frequently. The other option is to set up Google Sheets to push data into ClicData every time new data is modified or added. Learn how to do that in this tutorial.

This is especially powerful when using a sheet linked to a form or another service that has an inconsistent distribution of submissions throughout the day.

See it in action:

Using this tutorial, you will be able to create a live connection between your ClicData dashboards and your Google Sheets. Before we start, you’ll have to create a connector to your Google Sheets account and import the file you’d like to create a live connection for. Our Google Sheets connector guide should help!

Tutorial

This tutorial will require a basic understanding of advanced functions and scripting within the Google ecosystem. You do not need to know JavaScript to follow along, but it will help. This tutorial will make use of the ClicData API and the Google Apps Script platform to create a live connection between your Google Sheet and ClicData.

1. Enter Account Settings

The first step is to enter the Account Settings on your ClicData account (available to the account owner) and set up an application with the ‘OAuth 2 – Client Credentials’ authentication mode.

• When you first add a new application, you will be prompted to name and enable the application. We’ll just call the applications ‘Google Sheets’ and ensure that the toggle is enabled
• In the ‘Authentication’ tab, switch the authentication mode to ‘OAuth 2 – Client Credentials’ and record your Client ID and Client Secret for later use.

• It’s also a good idea to check that your user settings are set up to enable the use of the API. For the Client Credentials mode, you’ll have to make sure that your account owner’s user has the ‘Allow User to Use API’ setting enabled. To find this setting go to Security -> Users -> Edit Account Owner’s User.

2. Get your Schedule or Dataset ID

The next piece of information we’ll need is the ID of either our schedule or our dataset in ClicData. A quick way to do this is to use the API test functionality on our documentation page: https://app.clicdata.com/help/apidocumentation/api-docs

• Scroll down to the /data endpoint and click the lock icon to the right. This will pop up a prompt to authorize the page to access your API. We can use the Client ID and Client Secret we just set up in the step above.

• The first prompt is for Authorization Code mode, we will have to scroll down to the bottom to use the Client Credentials mode app we set up.

• Now that we’re authenticated, we can use the ‘Try it out’ button to get a JSON with all of the datasets on our account, and their metadata (including ID’s)!

Optionally, we can filter to data sets that can be refreshed, and even filter by name!

Once you have the list of data sets and their ID’s, find the one you want to connect (your Google Sheet) and copy the ID for later use.

3. Set Up Your Google Apps Script

Now that we have our application set up on ClicData, we can start to set up our Google Apps Script environment on the Google Sheets side of things.

• First, we will open the Google Sheet that we want to connect and select the Script editor from the Tools menu.
• Once in the script editor, we will need to add the OAuth 2.0 Apps Script library to our project. Select libraries in the Resources menu:

• Enter the script ID for the OAuth 2 library and click Add to import it

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF

4. Set Up Your Script

Now that our environment is ready, we can start setting up our script!

1. Copy/paste the code block below.
2. Replace the following text throughout the script to reflect the data we obtained above.

• ‘RecId’, replace the string with the id’s we obtained earlier in the defined SCHEDULE_ID or DATA_ID variables depending on which method you want to use.
• ‘id’, replace with your Client ID we obtained earlier.
• ‘secret’, replace with your Client Secret we obtained earlier.
• DATAREFRESH depending on whether you are refreshing your dataset as part of a schedule, or refreshing the data set directly, you should replace the variable call on line 34.

5. Time To Test!

Now that we have everything set up, time to test!

• Select the function ‘run’ and press the play button to give it a test!

• The first time you try to run your code, you’ll be prompted to review permissions on the script we just created. It’ll be treated just like any other app you’d connect to your Google account. You’ll get a warning that the app has not been verified, but this is okay! Since we made it ourselves it makes sense that it hasn’t been officially verified.

6. Automate Data Refresh in ClicData

Now we can refresh our data in ClicData directly from Google Sheets. All that’s left is to set up an automation that triggers on any Sheets-side event and we’re set. Fortunately, Google makes this extremely simple!

See that clock icon on the toolbar at the top of the script editor? That icon will bring us to the trigger editor for our project (already linked directly to our Google Sheet).

Simply click Add Trigger and set up your conditions. Ensure the function being run is the one called ‘run’. There will be several types of events you can trigger your data refreshes in ClicData based from. The two most important for most use-cases will be ‘On Edit’ and ‘On Form Submit’.

What do these options mean?

• ON EDIT – will trigger any time an edit is made to the Google Sheet. Depending on how frequent edits are this could get intense. Not recommended for sheets with many tabs and a significant number of users editing at once.
• ON CHANGE – Will trigger when a structural change is made to a spreadsheet, like adding a new row or column or deleting one. This is recommended for sheets that are continuously pasted into from other sources (note: this will not sync automatically when minor edits are made).
• ON FORM SUBMIT – Extremely powerful when used in conjunction with a Google Form. This will allow any new entries to automatically load directly in ClicData. This is what the example in the blog post is using.

That’s it! Now that your automation is set up in Google Apps Script your sheet should automatically sync any new data into ClicData. This will, of course, vary depending on which update trigger you went with.

---

Related Video – Connect Your Data from Google Sheets