How do you regularly import a Google Sheet into a Coda Table?

#1

Goal

I have a data table in a google sheet that I would like pulled into a coda table regularly vs copy and pasting. Ideally this happens every hour or so. I’ve searched throughout the forum, but no one seems to have a solution to replace the “copy and pasting” from Sheets. There are allusions to it, but I can’t find a single example of this being set up with Zapier.

What I’ve tried?

Setting up a “Get Many Spreadsheet Rows” Zap that pulls the spreadsheet in and then sends it to Coda.

Why doesn’t this work?

The data exported from that block seems to be in array form and inaccessible on a per field basis (ie I can’t do something like data.field_name to access one of the columns - it’s just a giant array of spreadsheet rows and gets plopped in a single Coda cell as a comma-separated list

Why can’t I just setup triggers for each row?

Because the data in the spreadsheet changes within existing rows all the time. I have about 20 cells I need to import and don’t want to create 20 zaps. I just want to pull the sheet in each our and do a lookup within Coda instead.

Anyone have any suggestions? Would really appreciate the help! I’m sure others need to do this as well - maybe I’m just missing something.

#2

like Coda’s version of importrange()?

#3

Yeah exactly, except it would have to be cross platform (ie Google Sheets -> Coda). I think Coda could use an internal importrange() function to share across coda documents as well however.

I ended up solving this problem by using a Zap:

  1. Time Trigger
  2. Google Sheets - Get many spreadsheet rows
  3. Run Javascript
  4. Coda -> Upsert

In the Run Javascript Zap, use the raw rows from the Google Sheets Zap:

Then write some JS to turn each row into a column:

const myData = JSON.parse(inputData.rows);

var a = {};
var b = myData.forEach(item => {
  let col = item[0];
  let key = item[1];
  let value = item[2];
  a[col] = value;
});

return a;

Then you upsert a row in coda with each Google Sheet row becoming it’s own column giving you a long row of columns that upserts on an upsert key that never changes.

In coda, you then haven to transpose that data back to rows from columns. Super long work-around but works in some cases.