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

Making Packs
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.

1 Like
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:

image
image.jpg800×553 71.8 KB

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.

4

@Philip_Johnson Did you find another way to do this by any chance? What do you mean by transpose? Is there an in built function or are you doing copy paste?

5

No unfortunately not - but my hack is still working.

Once in Coda, you’ll have a table with one long row. Mine is structured like

upsert key, updated_at, row 1 value, row 2 value, row 3 value, etc

Then, in a separate table, you re-define the same rows you want. IE

ID | Desired Row Name | Value
 1 | Row 1 Name       | (Formula that transposes the columns to rows for us based on ID in first column)

Formula that does the transposing (where [Google Sheets Data Flat] is the table containing the data from your zap):

[Google Sheets Data Flat].[List Column].Nth(1).nth(thisRow.ID)

The “List Column” refers to a formula column at the very end of the “Google Sheets Data Flat” table, that looks like this:

list([Google Sheets Data Flat].[1], [Google Sheets Data Flat].[2], [Google Sheets Data Flat].[3], [Google Sheets Data Flat].[4], [Google Sheets Data Flat].[5], [Google Sheets Data Flat].[6], [Google Sheets Data Flat].[7], [Google Sheets Data Flat].[8], [Google Sheets Data Flat].[9], [Google Sheets Data Flat].[10], [Google Sheets Data Flat].[11], [Google Sheets Data Flat].[12], [Google Sheets Data Flat].[13], [Google Sheets Data Flat].[14], [Google Sheets Data Flat].[15], [Google Sheets Data Flat].[16])

Yes, you have to manually create that formula, but that’s the only way I could get this whole mess to work. It wouldn’t be a great solution for a table with a ton of rows.

6

Hi @Philip_Johnson, if you’re open to using Google Apps Script, I posted these scripts yesterday as a potential solution to get data from Coda into Google Sheets and vice versa:

1 Like
7

Outstanding stuff Al! Great bit of code!

1 Like