Syncing Data from Coda to Google Sheets And Vice Versa with Google Apps Script [A How-To Guide]

Last year I published 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.

• Sync data from
• Sync data from

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 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

• 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)

• 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” in your Coda table that is set to true (more about this later in the post)

Setup: Coda to Google Sheets script

1. Go to  and create a new project and give your project a name.
2. Go to Libraries then Resources and paste the following string of text/numbers into the library field: 

   15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

   .
3. Click Add and then select a version of the library to use (as of May 2020, version 8 is the latest)
4. Copy and paste the  into your Google Apps Script project and click File then Save.
5. Go to your Coda , 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

   .
6. 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 . Copy and paste your doc ID into 

   YOUR_SOURCE_DOC_ID

   .
7. Go back to your  and scroll down to the very bottom until you see “Labs.” Toggle “Enable Developer Mode” to ON.
8. 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

   .
9. 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  for more info.
10. 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.
11. 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

    .
12. 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.
13. 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 .
14. 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

1. You can follow steps 1–10 above to fill out to in the script (except 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.
2. 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.
3. 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

   .
4. 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 of the script as well.
5. 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 . 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

   .
6. Follow steps 12–14 to set up your time-driven trigger.Use cases with Google Sheets

Use cases with Google Sheets

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

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 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 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 (see ).

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 , 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 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

Add Coda’s library for Google Apps Script

Syncing a Coda doc to Google Sheets

• 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 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 . 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.

Column names in Google Sheets

The one exception is the

TARGET_SHEET_SOURCE_ROW_COLUMN

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

browserLink

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

StaffID

Unique ID column in Google Sheets

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

Fabricating a unique ID in Google Sheets

Creating your own unique ID in Google Sheets

In the above screenshot,

Feature

Fabricated ID

Feature

Team

Milestone

Feature

1. 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)
2. The columns you have concatenated may change (in this case, the

   Team

   or

   Milestone

   may change which would ruin the uniqueness of the ID)

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 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

Project

The

sortCodaTableCols()

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

Project

Adding and deleting rows

The main

runSync()

addDeleteToSheets()

updateSheet()

TARGET_SHEET_SOURCE_ROW_COLUMN

The columns in green 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

Manual Enter

Source Row URL

Updating rows

The

addDeleteToSheets()

updateSheet()

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 and would prevent the need for the

addDeleteToSheets()

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()

A little helper sort function

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

I created this little

sortArray()

targetRows

sort()

sortArray()

sortedTargetRows

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()

compareFunction

sortArray()

sort()

WTF?

Now if you sort this list of values in a spreadsheet or Coda table, you’ll get the results in the

What you expect

sort()

toUpperCase()

NPmgrG

NPMGRG

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

• 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.D
• o 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 for the 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 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

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 😬.

• Formulas don’t sync — Probably not a huge surprise as this is also a limitation of the 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 , 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

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

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

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

The

allRows[‘targetRows’]

currentCodaRows

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

Deleting and re-writing rows each time

1. Delete all rows in the target
2. Copy all the rows from the source
3. 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 script, the script is broken down into

addDeleteToCoda()

updateCoda()

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

true

The

REWRITE_CODA_TABLE

2PACX

Getting permissions from Google Sheets

Instead of having to remember if you need to switch the

REWRITE_CODA_TABLE

true

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

true

Final Caveats & Notes

Using simple triggers in Google Apps Script

Rate limits

V8 runtime

These scripts utilize the 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

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.

Source: Ben Collins

Not a programmer