Al Chen for Coda

Posted on May 4, 2020 • Originally published at coda.io on May 4, 2020

How to sync data from Coda to Google Sheets (and vice versa) with Google Apps Script tutorial

#googlesheets #coda #googleappsscript #tutorial

Two new scripts

Last year I published a tutorial on how to sync data between two Coda docs and data between two Google Sheets. What was missing from the tutorial was how to sync data between a Coda doc and a Google Sheet. Writing these scripts was definitely more challenging than the original script I wrote for syncing two Coda docs since the data model for Coda is different from Google Sheets. Please read the caveats below about these scripts to learn about some of the roadblocks I encountered when writing these scripts.

If you are reading this, chances are you have a lot of experience with Google Sheets, Coda, and perhaps the Coda API. I’m going to skip the introduction to Coda as I did with the last tutorial and get straight to the point on how you can:

Sync data from Coda -> Google Sheets
Sync data from Google Sheets -> Coda

If you want to skip right to using the Google Apps Scripts, go to the other two pages in this doc (mentioned above) or go to this repo which contains all four scripts for syncing data (PRs welcome). Here are two video tutorials if you prefer a visual tutorial.

Coda to Google Sheets

Google Sheets to Coda

Features

There are some limitations to the scripts which I’ll discuss later on in this blog post, but these are the main features for each script:

Coda -> Google Sheets

New rows that get added or deleted in your Coda table will also get added or deleted in Google Sheets
Existing rows that get updated in Coda will also get updated in Google Sheets
You can re-arrange the columns in your Google Sheet and the sync will still sync the appropriate columns in your Google Sheet
You can add or insert new columns in your Google Sheet and write formulas in these new columns
You can add or insert new columns in your table in Coda and these columns won’t get synced to Google Sheets (unless you create a new column in Google Sheets with the same column name as the one in your Coda table)

Google Sheets -> Coda

New rows that get added or deleted in your Google Sheet worksheet will also get added or deleted in your Coda table
Existing rows that get updated in your Google Sheet worksheet will also get updated in Coda
You can sort and filter the rows in your target Coda table and the script will still add, delete, and update the appropriate rows in Coda
You can add rows to your Coda table and not get them deleted on the sync by adding a “Do not delete” checkbox column in your Coda table that is set to true (more about this later in the post)

Some of the features in the Coda -> Google Sheets script also apply to the Google Sheets -> Coda script, but I haven’t fully tested every use case. If you see any bugs, please add them to the repo’s issues list.

Setup: Coda to Google Sheets script

Starting in line 9 to line 14 of the coda_to_sheet.js script, you’ll need to enter in some of your own data to make the script work. Step-by-step:

Go to script.google.com and create a new project and give your project a name.
Go to Libraries then Resources and paste the following string of text/numbers into the library field: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl.
Click Add and then select a version of the library to use (as of May 2020, version 8 is the latest)
Copy and paste the entire script into your Google Apps Script project and click File then Save.
Go to your Coda account settings, scroll down until you see “API SETTINGS” and click Generate API Token. Copy and paste that API token into the value for YOUR_API_KEY in the script. Note: do not delete the single apostrophes around YOUR_API_KEY.
Get the the doc ID from your Coda doc by copying and pasting all the characters after the _d in the URL of your Coda doc (should be about 10 characters). You can also use the Doc ID Extractor tool in the Coda API docs. Copy and paste your doc ID into YOUR_SOURCE_DOC_ID.
Go back to your account settings and scroll down to the very bottom until you see “Labs.” Toggle “Enable Developer Mode” to ON.
Hover over the table name in your Coda doc and click on the 3 dots that show up next to your table name. Click on “Copy table ID” and paste this value into YOUR_SOURCE_TABLE_ID.
To get your Google Sheets ID, get all the characters after /d/ in your Google Sheets file up until the slash and paste this into YOUR_GOOGLE_SHEETS_ID. See this link for more info.
Write in the name of the worksheet from your Google Sheets file where data will be synced into in the YOUR_GOOGLE_SHEETS_WORKSHEET_NAME value.
In Google Sheets, create a new column name at the end of your column headers called something like Coda Source Row URL and make sure there is no data in that column below the header. Write that column name in YOUR_SOURCE_ROW_URL_COLUMN_NAME.
Go back to Google Apps Script, click on the Select function dropdown in the toolbar, and select runSync. Then click the play ▶️ button to the left of the bug 🐞 button. This should copy over all the data from your Coda doc to Google Sheets.
To get the script to run every minute, hour, or day, click on the clock 🕒 button to the left of the ▶️ button to create a time-driven trigger.
Click Add Trigger, make sure runSync is set as the function to run, “Select event source” should be Time-driven, and play around with the type of time based trigger that fits your needs. I like to set the “Failure notification settings” to Notify me immediately so I know when my script fails to run.

Setup: Google Sheets to Coda script

Most of the steps above apply to the sheets_to_coda.js script as well but there are few extra features.

You can follow steps 1–10 above to fill out line 12 to line 18 in the script (except line 14 mentioned in the next step). The main difference is that “SOURCE” and “TARGET” are flipped around since you are now syncing from a source Google Sheet to a target Coda doc.
Your Coda table cannot have a column named Coda Row ID. If you need to use a column with this name, replace the TARGET_ROW_ID_COLUMN variable with another value.
If you have edit access to the Google Sheet, follow step 11 above and write in the column name in YOUR_SOURCE_ROW_URL_COLUMN_NAME.
If you want the ability to add rows to your Coda table and NOT have these rows deleted every time the sync runs, create a column in your Coda table and name it Do not delete. This column should be a checkbox column format and you will check the box for every row you manually add to your Coda table that you want to keep in that table. Otherwise, the script will delete that row and always keep the Coda table a direct copy of what’s in your Google Sheets file. If you change the name of this Do not delete column, you must edit the value of the DO_NOT_DELETE_COLUMN variable in line 22 of the script as well.
If you want the script to completely delete and re-write the rows in your Coda table each time the script runs, set the REWRITE_CODA_TABLE to true in line 23. This may make the script run faster, but may not be faster for larger tables (few thousand rows). For Google Sheets files where you only have view-only access, this setting will automatically get set to true.
Follow steps 12–14 above to set up your time-driven trigger.

Use cases with Google Sheets

Some of the most common use cases for integrating your application with Google Sheets can be found in the G Suite Marketplace for Google Sheets. From a business perspective, being able to visualize your data in Google Sheets allows you to slice and dice your data in ways you cannot do in on platform like Salesforce, for instance (FYI there’s a Salesforce add-on for Google Sheets).

The opposite is true too. Your team or company’s data may be stored in a Google Sheet but the data just sits there without being “actionable.” Let’s say you have a bunch of customer information and you want to create mailing labels with your customers’ names and addresses. Being able to “export” your data from Google Sheets into a mail merge application like Avery will make it easy to create the mailing labels you need.

Then there’s the pinnacle of productivity in Google Sheets: keeping data synced between your application and Google Sheets at all times.

When Google Sheets first came out, it was a game-changer since changes you make on your browser are instantly reflected in your colleague’s file. We have come to expect this with tools we use in the browser. But having data synced between Google Sheets and your other applications at all times is less common, and this is why the Google Sheets API is so important. From a Coda perspective, there are several use cases you might want to keep your Coda doc synced with a Google Sheet (and vice versa):

Data synced from your Google Sheet

HR & recruiting — All your candidates are stored in a Google Sheet but you want to be able to move candidates through different stages in the interviewing pipeline and Google Sheets isn’t sufficient for your needs. Having all your candidates in a table in Coda means you can use templates like this one to manage candidates more effectively.
E-commerce and ERP — Orders, customers, and POs may all be different tabs in a Google Sheet that gets updated through Shopify or some other e-commerce platform. In order to manage your e-commerce business, you may want to see charts, calendar of shipments, and reports that Google Sheets cannot provide easily. Syncing the data from Google Sheets to Coda means you can do ERP properly (see this template as an example).
Customer Feedback — You may have a ticketing system like Zendesk or Intercom and all feedback lands in a Google Sheet somewhere. You can do some basic analytics in the Google Sheet but to reply to the feedback means you have to go into Gmail and start replying to customers. If your customer feedback is all in a Coda doc, you can run analytics and send emails using the Gmail Pack (see this template).

Data synced to your Google Sheet

3rd-party vendor reporting — Your vendors may not be using Coda yet, but you have all your vendor data in Coda and need to send them the data in a format they prefer. While you could publish your Coda doc, the vendor still wants the data in a Google Sheet you have edit access to.
Data “backup” — Your team may create thousands of rows of data every quarter in a Coda doc and want to start each quarter “fresh.” Coda docs grow with your teams and they may get slow as you add in more functionality, so having a backup of your data in Google Sheets is another reason to sync data from your Coda doc to Google Sheets.
Finance & Accounting — Most internal finance and accounting functions still use Excel and spreadsheets for month-end reporting, taxes, and other business-critical activities. As your data grows in Coda, you can keep your finance counterparts in the loop by having your data synced to a Google Sheet which your finance team can use for their reporting and forecasting purposes.

Setting up Google Apps Scripts

Before you start using the scripts to sync data from Coda to Google Sheets or vice versa, you need to have Google Apps Script setup correctly. Just navigate to script.google.com and click on New Project. You’ll land in the GAS script editor. At this point, click on Resources→Libraries in the toolbar and you’ll want to paste in the following Coda library for Google Apps Script:

15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

After you add the library, you can pick a version of the library to use (I just picked the latest version to take advantage of all the latest features in Coda’s API):

*Add Coda’s library for Google Apps Script*

Syncing a Coda doc to Google Sheets

Setting up the script for syncing a table from a Coda doc to a Google Sheets requires a few simple inputs. I walk through how to get some of these inputs in my previous tutorial, so read that if you have any questions on how to get the following inputs:

Coda doc ID: This is the string of characters after the _d in the URL of your Coda doc
Coda table ID: The unique ID for the table you want to sync from in Coda. If you have Enable Developer Mode turned on in your account settings, you can get the table ID by simply clicking the 3 dots next to your table:

Google Sheet ID — This is the string of characters after the /d in the URL of your Google Sheet (see documentation here on how to get this ID).
Google Sheet worksheet name — Name of the individual worksheet in your Google Sheet you want to sync data into from your Coda doc
Source Row Column — This is the only customization you’ll have to do to your Google Sheet. You’ll need to add a column (typically the last column in your Google Sheet) that’s called something like Coda Source Row URL. This is the name used in the script. This is an important column to have in your Google Sheet since it will store the unique URL to a row in your Coda table. More about this later.

Once you have these inputs, you’re ready to get started with syncing your data!

Column names in Google Sheets

Try to keep the name of the columns in your Google Sheet the same as the columns in your Coda table. All the columns you want to sync from your Coda table to the Google Sheet should have its own column.

The one exception is the TARGET_SHEET_SOURCE_ROW_COLUMN variable which you’ll see in the script. Whatever value you put in this variable should also be the name of the column in your Google Sheet. You should put this column at the end of your table in Google Sheets like so:

*Source row column to put in your Google Sheet*

This column will be overwritten by the Google Script with the unique source row URL from Coda (every row in a Coda table has a unique identifier). The reason why we need this column for the source row URL is so that the script knows which rows have been added to the Google Sheet so that if you delete any rows in the source Coda doc, those rows can be deleted in the target Google Sheet. This brings me to a quick aside about the benefits of these source row URLs (these are called browserLinks in the API).

A unique row identifier

If you are a heavy user of Google Sheets, you may find yourself creating a “unique ID” column in table so that when you reference that row somewhere else in your Google Sheet, you can do a VLOOKUP to pull all the data related to that row. Sometimes you can get away with a column of data (maybe it’s a customer name, task name, or project name). For instance, in this screenshot the unique ID is the StaffID column:

To cover the cases where your table does not have a unique ID, the script puts the unique row URL from Coda into the TARGET_SHEET_SOURCE_ROW_COLUMN to act as the unique identifier. The Google Sheets -> Coda script also utilizes this column (assuming you have edit access to the Google Sheet). In lieu of this unique ID column, there’s no way for the script to know which rows have been added to the Google Sheet from Coda since there’s no native row ID system in Google Sheets (see this thread).

Fabricating a unique ID in Google Sheets

One alternative to fabricate this unique identifier in your data set is to concatenate a bunch of columns together in hopes that this new column will be the unique ID for that row:

*Creating your own unique ID in Google Sheets*

In the above screenshot, Feature is actually a pretty unique column of data. But to be 100% sure, there’s a Fabricated ID column which concatenates Feature, Team, and Milestone to create a “more unique” ID in the event there are two Features with the same name. This is not a perfect method due to two reasons:

The fabricated ID column might not be unique enough and it might be duplicated in other rows (which means you would have to concatenate more columns of data to fabricate that unique ID)
The columns you have concatenated may change (in this case, the Team or Milestone may change which would ruin the uniqueness of the ID)

In a previous life as a financial analyst, I employed this fabricated ID trick quite often but I had to choose the columns wisely. Typically in a report that has a time series, this would involve picking a dimension (e.g. west region), metric (e.g. sales), and the date for that specific row. This worked for static reports where data wasn’t getting deleted or updated too often. It’s a lot more risky to utilize this strategy with a shared Google Sheet with your team where data is constantly changing. Choose your columns wisely if you go down this path.

Mixing columns in Coda

The advantages of having a unique identifier for the rows in Coda also applies to columns in Coda as well (this benefit is realized in the Google Sheets -> Coda script). For syncing Coda to Google Sheets, the script has to use the actual names of the columns in Google Sheets since there’s also no native column ID in Google Sheets. This means if your column in Coda is named Projects but you accidentally misspell the column name in Google Sheets to Project, the data will not sync over correctly from Coda to Google Sheets.

One feature of the script is that you can re-order the columns in Coda and the data will still sync over correctly based on the column names. So your tables in Coda and Google Sheets could be organized like this, and the sync would still work:

The sortCodaTableCols() function re-arranges the columns in Coda to reflect the order of the columns in Google Sheets by simply looking for the column name in Coda:

var headerCodaTable = sourceRows[0]['cells'].map(function(row) { return row['column'] });
  var sheetsColOrder = [];

  headerRow.map(function(col) {
    sheetsColOrder.push(headerCodaTable.indexOf(col))
  })

  var sortedSourceRows = sourceRows.map(function(row) {
    var cells = sheetsColOrder.map(function(col) {
      if (col == -1) {
        return {        
          column: null,
          value: null,
        }
      } 
      else {
        return {
          column: headerCodaTable[col], 
          value: row['cells'][col]['value'],
        }       
      }
    });
    return {cells: cells}
  })
  return sortedSourceRows;

This means you can have your own “custom” columns in Coda or Google Sheets which can even contain formulas, and they won’t corrupt the sync from Task, Team, and Project to their respective columns in Google Sheets. As long as these custom column names in Coda or Google Sheets don’t show up in the other platform, then you can do whatever you want with these custom columns:

This could be useful if you work with a vendor who needs to see data in a Google Sheet to perform certain calculations that could be meaningful to them but don’t really matter to you and your Coda doc. As long as there isn’t a column name in the Google Sheet that matches the name of a column in your Coda table, then everything will work as intended.

Adding and deleting rows

The main runSync() function runs two other functions: addDeleteToSheets() and updateSheet(). The logic here is to add any new rows from Coda to Google Sheets and delete any rows from Google Sheets that were deleted from Coda. As mentioned above, the script uses a TARGET_SHEET_SOURCE_ROW_COLUMN to keep track of all the unique rows that need to be synced from Coda to Google Sheets.

An added benefit of using this “source row column” in Google Sheets is that you can add new rows of data to Google Sheets manually and leave the “source row column” blank. When the sync runs, the script essentially skips these new rows because they don’t have a URL that maps to an existing row in Coda. I’m not sure about the exact use case for when you would want to do this, but perhaps your Coda doc keeps track of sales from a store and your accounting team gets the data synced to a Google Sheet like this:

The columns in yellow are the ones that get synced from your Coda doc. The first 3 rows get synced correctly because you see values in the Source Row URL column. The accounting team realizes that there are more sales that were not accounted for and don’t exist in your Coda doc. They might manually add rows 5 and 6 and have a column they use internally called Manual Enter to keep track of the rows they are manually adding to the Google Sheet. When the sync runs next, rows 5 and 6 won’t get overwritten or deleted because they left the Source Row URL column blank.

Updating rows

The addDeleteToSheets() function was relatively simple to write, but updateSheet() was much more difficult given that rows in Google Sheets might be sorted in all kinds of ways. Additionally, I felt that scanning the entire Google Sheet for a source row URL and then scanning each column value to see if an update is needed was inefficient. Even if you have only 100 rows in your Coda doc that you want to sync to Google Sheets, that means there could potentially be 10,000 comparisons just for the row URLs alone every time the sync runs.

One option I considered was just blowing up the entire list of data in Google Sheets first (deleting all the rows) and re-writing the data from Coda to Google Sheets. This also didn’t feel right because for larger tables this could potentially hit Google Apps Script rate limits and would prevent the need for the addDeleteToSheets() function, prevent the need for the “source row column” in Google Sheets, and wouldn’t allow the user to manually add rows to the Google Sheet because those rows would get wiped out on the sync.

My thinking was to create two 2-D tables that were sorted exactly the same. The first table contains the rows from Coda that also exist in Google Sheets. The second table contains the rows in Google Sheets. The tables would contain the same number of rows and columns so you could then do a sequential comparison between the source Coda table and the target Google Sheet and see if there are any updates that need to be made in the Google Sheet.

The first thing to do was to convert the row objects in Coda to a 2-D table that is more similar to Google Sheets’ row objects. The convertValues() function “flattens” the Coda row object so that each row object simply contains an array of column values:

Most of the work in these scripts is actually just data munging so that the data is in format that is acceptable for Coda and Google Sheets. Once the tables are sorted in the same order in terms of rows and columns, the script can now check cell by cell if there are any chances that need to be synced over to Google Sheets.

I felt this sequential comparison of cells between the Coda and Google Sheets table was more performant than scanning for each row URL. The number of comparisons between the source and target tables is limited to the number of “cells” in either table. In this example, the script would only have to make 15 comparisons before figuring out that there are three cells in Coda that have been updated and need to be synced over to Google Sheets:

While this may seem like a performance boost, there is a lot of pre-processing to get the rows sorted correctly, so the net result might be same in terms of rows and cells scanned. There much more smarter people out there who understand sorting algorithms, so there may be an even more efficient approach here 🤷‍♂️.

A little helper sort function

In order to get the tables sorted perfectly before doing the cell by cell comparison, I needed to figure out a way to sort an array of arrays by some value. In this case, we have a bunch of arrays of column values that represent our rows, and the unique ID we want to sort on is the source row URL:

*How do we sort each row object by the 7th element (row URL)?*

I created this little sortArray() function that’s one of the workhorses in the script. It seems like such a common problem and I was surprised there wasn’t a built in sort function to sort an array of arrays (or maybe I just didn’t search hard enough). So if I want to sort the targetRows object below which contains all the rows in my Google Sheet, I run the sort() function on it and pass in the sortArray() function and the returned sortedTargetRows object is…as you expected…sorted by the source row URL:

var sortedTargetRows = targetRows.**sort(sortArray)**;

function sortArray(a, b) {
  var x = a[rowURLIndex];
  var y = b[rowURLIndex];
  if (x === y) {
    return 0;
  }
  else {
    return (x < y) ? -1 : 1;
  }
}

One thing I learned about the sort() function is that if you pass in what they call a compareFunction (in my case the sortArray() function), to sort values by alphabetical order, it actually sorts in alphabetical order for values with uppercase letters followed by lowercase letters. Here is a list of values and how you expect them to be sorted versus how the sort() function actually sorts stuff:

Now if you sort this list of values in a spreadsheet or Coda table, you’ll get the results in the What you expect column. I couldn’t figure out why the sorted values didn’t match up with what I expected after sorting the values in Google Sheets. Then after some debugging I realized this is the default behavior of the sort() function in JavaScript. A common workaround is to apply the toUpperCase() function to the value so that you are doing a case-insensitive sort. Unfortunately, this won’t work for the script because it’s possible for a table in Coda to have two row IDs with the same order of six characters but just be capitalized differently (e.g. a row ID of NPmgrG and NPMGRG could exist in the same table).

In our case, we need to find a case-sensitive sort to account for the uniqueness of row IDs. I searched for a function like this to no avail. Then I realized it doesn’t matter if the script doesn’t sort the table in the alphabetical order I expect as long as it applies the same “incorrect” sort to both the source and target tables equally. This means both tables will still be sorted in the same order just not in the order we expect from a typical sort in Google Sheets or Excel.

Syncing Google Sheets to a Coda doc

After writing the Coda -> Google Sheets script, I thought the Google Sheets -> Coda script would be a breeze since I had written all the functions to convert and sort data. All I would have to do is just switch around some variables and everything would work out just fine. Turns out I was completely wrong since there are a bunch of edge cases to account for in Google Sheets that makes the sync a little more difficult compared to Coda to Google Sheets.

You can follow most of the steps in the Coda to Google Sheets setup to get the values you need for the script to run, but there are a few caveats and extra options you can set to get similar functionality as the Coda -> Google Sheets script:

Target Row Id Column — This is a key that stores each row’s unique ID from a Coda table. This value is in the “source row URL” (last 6 characters). Be default this variable is set as “Coda Row ID,” so make sure you don’t have a column in your Coda table with this name.
Do Not Delete Column — Unlike Google Sheets, the script is not written in a way where you can add additional rows to the target Coda table without having them deleted when the sync runs. As mentioned above for the Coda -> Google Sheets script, you can add rows to your target Google Sheet and not have them deleted on the sync. You need to create a checkbox column in your Coda table called Do not delete and check off the box for that row if you don’t want it to get deleted on the sync. If you prefer a different column name, just change the value for the DO_NOT_DELETE_COLUMN variable.
Rewrite Coda Table — Unlike the Coda -> Google Sheets script, you have the option to completely delete all the rows in your target table and re-write them with all the rows from your source Google Sheet. Set the REWRITE_CODA_TABLE variable to true if you want this behavior (may result in a faster sync).

Column and row limitations

If you have edit access to the Google Sheet, you will need to add a column at the end of your table called something like “Source Row URL” similar to the “Coda Source Row URL” pattern mentioned above for the Coda -> Google Sheets script. After writing the data from Google Sheets to Coda for the first time, the unique row URLs from Coda are copied over into this “Source Row URL” column in your editable Google Sheets. Obviously this doesn’t apply to Google Sheets where you only have read-only access (more on that later).

One limitation of the script is that if you add a new column to the Google Sheet, you also need to add that same column name to the Coda table. It’s ok if the column order isn’t the same in Coda, but that column name just needs to exist somewhere in the Coda table. You can just hide the column in Coda to make the table nice and clean. This is actually a limitation caused by the way I structured the script, so hopefully it doesn’t cause you too much inconvenience 😬.

Be careful with empty rows in your data in Google Sheets because those rows also get “synced” over to Coda. Not only will those empty rows show up in your Coda table, they will get their own source URLs. Ideally, the Google Sheet won’t contain any empty rows and this won’t be a problem for you.

A few other “small” things:

Formulas don’t sync — Probably not a huge surprise as this is also a limitation of the Coda -> Google Sheets script. Any columns with formulas you sync over to Coda will just be hard-coded to that column in your Coda table.
Resetting column formats — When your Coda table is blank and you’re syncing over rows for the first time from Google Sheets, you may have to change some of the column formats to the proper format. For instance, if your dates in Google Sheets are in Zulu format, Coda will sometimes interpret these values as a select list. After the sync, just change the column format in Coda to the Date format you want and future syncs will work just fine.
You can’t sort your Google Sheet — The script looks for empty source row URLs in the SOURCE_SHEET_SOURCE_ROW_COLUMN in your Google Sheet and it scans that column until it finds an empty value to start pasting in new source row URLs from Coda. If you sort your table, that column will get all jumbled and the script will break. New rows that you add to the Google Sheet should have the source row URL column blank and these blank cells need to be contiguous.

Setting a timer for source row URLs

You will notice that the data syncs over pretty quickly to Coda, but the SOURCE_SHEET_SOURCE_ROW_COLUMN (aka the “source row URL”) takes a couple seconds to show up in your Google Sheet. The reason this happens is because of the steps that need to happen for this sync to work:

Find the rows that need to be added from Google Sheets to Coda
Insert those new rows into Coda
Coda snapshots the new data added to your table
Look to see if the source row URLs have shown up in the Coda table
Copy over the source row URLs to Google Sheets once those URLs show up

The key step in #3 since that snapshot can take a few seconds to happen. If we try to copy the source row URLs right after the rows are inserted into the Coda table, the script will come up with nothing an no row URLs will show up in your Google Sheet.

To get around this, I added a little sleep timer to basically check for the source row URLs every two seconds:

while(currentCodaRows.length <= allRows['targetRows'].length) {
  timer += 2;
  if (timer == 60) { break; }
  Utilities.sleep(2000);
  currentCodaRows = retrieveRows();    
}

The allRows[‘targetRows’] object contains all the rows in your Coda table when the script runs for the first time. Every two seconds, the loop retrieves the rows in the Coda table in hopes that the the number of currentCodaRows has exceeded the number of original rows when the script first ran. The loop also breaks after 30 seconds if, for some reason, the Coda API cannot retrieve all the number of current rows added to the table. So far it hasn’t taken more than five seconds for the URLs to show up, but this is on a small data set of a 5–10 rows being added each time I tested the script.

This sure seems like a heck a lot of work just to added some new rows to a table in Coda. That’s why I put in a REWRITE_CODA_TABLE variable to override all this source row URLs business.

Deleting and re-writing rows each time

As discussed with updating rows in the Coda -> Google Sheets script, I wanted to avoid this pattern of syncing data:

Delete all rows in the target
Copy all the rows from the source
Insert the copied rows into the blank target table

It didn’t seem like the right solution especially for a large table of thousands of rows because if you’re only changing or adding a few rows, the script has to delete and re-add all these thousands of rows. The simplicity of this approach is tempting, nonetheless. Just like the Coda -> Google Sheets script, the sheGoogle Sheets -> Coda script is broken down into addDeleteToCoda() and updateCoda() functions. The former function adds and deletes rows while the latter updates any existing rows in Coda that may have changed in the source Google Sheet.

Blowing up the Coda table each time the sync runs would prevent the need for individual functions that add, delete, and update because the nature of blowing something up is that you can re-build from scratch. I haven’t measured which option is more performant but my hunch is that for smaller tables of data, setting REWRITE_CODA_TABLE to true may actually make the script run faster at the expense of not having the source URLs in your Google Sheet.

The REWRITE_CODA_TABLE option is actually important for Google Sheets files you only have read-only access to. By default, you can’t write source row URLs to a Google Sheet you have view-access to, so there’s no point in using source row URLs to figure out which rows need to be added, deleted, and updated. Side note: the script doesn’t work on Google Sheets that have been published to the web. You’ll know the Google Sheet is published when the URL has a 2PACX in the URL like so:

Getting permissions from Google Sheets

Instead of having to remember if you need to switch the REWRITE_CODA_TABLE variable to true when you’re syncing from a read-only Google Sheet, I did a little hack to get the permissions you have on the Google Sheet by trying to add the logged in user (you) as an editor to the Google Sheet:

function sheetsPermissions() {
  try {
    fromSpreadsheet.addEditor(Session.getActiveUser());
  } 
  catch (e) {
    REWRITE_CODA_TABLE = true; // If no access automatically rewrite Coda tables each sync
  }
}

If you have edit-access to the Google Sheet, nothing happens since you are already an editor. If there is an error, then that means you don’t have permissions to add yourself as an editor to the Google Sheet (which means you only have read-only access). Int this case, the REWRITE_CODA_TABLE is set to true and the script goes on and blows up the Coda table and replaces it brand new with data from your Google Sheet.

Final Caveats & Notes

There are many other variables to consider before implementing these scripts into your daily business-critical processes, but I think the given feature set should get you 90% of the way there. Having said that, there are a few more things to think about and small limitations about the scripts in general I’ve discovered along the way. This is by no means an exhaustive list.

Using simple triggers in Google Apps Script

I thought that the Google Sheets -> Coda script could take advantage of simple triggers to fire off the script. Basically you could have the script fire right when you make an edit to any cell, the moment the Google Sheet loads, etc. Unfortunately, there are a few restrictions to using simple triggers, and it looks like the script has to be entirely contained in Google Sheets to utilize simple triggers. Additionally, I don’t think the script could keep up with the speed of edits if you are looking for near real-time syncing. Data would just get choked as the script waits for source row URLs to appear and data would start pouring into your Coda doc.

Rate limits

There are rate limits for Google Apps Script as well as Coda. I’ve tried syncing tables with 10,000 rows in both scripts (6 columns) and they both seem to work. I think in one test the Google Sheets -> Coda sync resulted in some rows missing in the Coda table. For the first time you sync data over, I’d recommend just doing a regular copy and paste instead of relying on the sync to copy all the data over correctly. Most likely, subsequent additions and edits would be as large so the sync should run smoothly.

V8 runtime

If you have existing Google App Scripts, you may have noticed this fun error message at the top of your editor:

These scripts utilize the V8 runtime which takes advantage of a bunch of modern JavaScript features. The only changes I needed to make to upgrade the scripts was changing the syntax for for each loops.

Moving off Coda or Google Sheets to a dedicated database

It’s tempting to use a Google Sheet or Coda doc as your de facto database. The interface is familiar, easy to edit and use, and it lives in your browser. The danger is when it feels so convenient that you start putting thousands or hundreds of thousands of rows into your spreadsheet and maybe rely on Zapier or these Google Apps Scripts to sync data in and out of other applications you use every day to get work done.

If the process isn’t business-critical and your team can put up with this annoying little thing:

…then by all means continue doing what you’re doing and pass the Google Sheet to the next intern or analyst who has to put up with updating it in the future. I would consider migrating your data to a dedicated database platform (like Google BigQuery) which has a nice integration with Google Sheets. Lots more to say about this subject, but I’ll just leave it at that.

Not a programmer

Most of this post is me pretending to know what I’m talking about. I’m not a programmer, and the scripts could probably be improved 10X by someone who actually knows what they’re doing and understands how algorithms work. There are unnecessary loops and bugs stamped all over the scripts so please proceed with caution ⛔️. If you happen to be someone who knows more about this stuff than me, consider contributing to the code. I just did the bare minimum to get something to work and hopefully these scripts will be sufficient to get you on you your merry way of not having to copy and paste between Coda and Google Sheets 🤙.

DEV Community