Instant query of Google Sheets

Hi,

I have a massive product database that I cannot store in Coda. It’s within the size limits of Google Sheets.

I have never created a pack before but I was wondering whether it is possible to do the following:

  • Have a search form in Coda
  • Upon clicking submit a custom pack queries a spreadsheet to return one or more rows into a result table

I have tried using Zapier but it is too slow for this task.

Is the above feasible if I learn how to create a pack?

Thanks in advance.

Shaheed

2 Likes

You know who you should ask - @Irfan_Khan

He did something marvelously similar in a seemingly elegant fashion utilizing Integromat and webhooks I believe??

Still hoping he’ll give more insight into his process . . .

4 Likes

Seems like you could do this with a Pack, but the exact details of how may be somewhat custom to your use case.

As a demo, here’s a somewhat awkward formula that gets multiple ranges from a spreadsheet when you call it like this: batchGet(’[spreadsheetId]’, [‘A2:C’, 'E10:E19])

import * as coda from "@codahq/packs-sdk";

export const pack = coda.newPack();

pack.addNetworkDomain("content-sheets.googleapis.com");

pack.setUserAuthentication({
  type: coda.AuthenticationType.OAuth2,
  authorizationUrl: "https://accounts.google.com/o/oauth2/v2/auth",
  tokenUrl: "https://oauth2.googleapis.com/token",
  scopes: ["https://www.googleapis.com/auth/spreadsheets.readonly"],
});

// The free-form schema-less data from sheets doesn't maps so well
// to the more structured tables of Coda, so in practice you'd probably
// want to use your application's data type instead of something awkward
// like the array of array of numbers data type below.

const ValueSchema = coda.makeSchema({
  type: coda.ValueType.Array,
  items: { type: coda.ValueType.Number },
});

const RangeValuesSchema = coda.makeObjectSchema({
  properties: {
    range: { type: coda.ValueType.String },
    values: {
      type: coda.ValueType.Array,
      items: ValueSchema
    }
  },
  idProperty: "range",
  displayProperty: "range",
});

const MultiRangeValuesSchema = coda.makeSchema({
  type: coda.ValueType.Array,
  items: RangeValuesSchema,
});

pack.addFormula({
  name: "batchGet",
  description: "Gets multiple ranges of values from a sheet",
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "spreadsheetId",
      description: "Spreadsheet ID",
    }),
    coda.makeParameter({
      type: coda.ParameterType.StringArray,
      name: "ranges",
      description: "List of ranges to fetch",
    }),
  ],
  resultType: coda.ValueType.Object,
  schema: MultiRangeValuesSchema,
  execute: async function ([spreadsheetId, ranges], context) {
    const result = await context.fetcher.fetch({
      url: `https://content-sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}/values:batchGet?` + ranges.map(r => 'ranges=' + encodeURIComponent(r)).join('&'),
      method: 'GET',
    });

    return result.body.valueRanges;
  },
});

To actually be able to run this code you’d need to set up a Google API project at Google Cloud Platform, add an OAuth2 client with “https://coda.io/packsAuth/oauth2” as a redirect URI, enable the Sheets API for your Cloud console project, and copy the client ID & client secret into your Pack configuration in the “Settings” tab for the Pack

When you log into sheets with your OAuth2 client ID you’ll also have to force your way through a big warning that tells you the app isn’t verified with Google, but then once you’re logged in you won’t have to worry about it

4 Likes

@Scott_Collier-Weir yea i think it would work here!

Heres an example i made for you @Shaheed_Fazal , I included a video so you can see the backend of it.
an embeded view here doesnt work for the automation since thats for the real doc
basically its this:

  1. Enter Search
  2. Click Search Button… which:
    a- deletes all the rows in the table
    b- runs a webhook with the search query appended to the url
  3. this runs an Integromat/Make scenario the searches the google sheets data and (in this example) searches all columns, case insensitive
  4. the data is aggregated to JSON
  5. the JSON is batch pasted into the Coda table

SEARCH WONT WORK IN THIS SAMPLE!

edit: ive been pretty busy with work and have vacation soon so apologies if im slow to reply

6 Likes

Thanks @Scott_Collier-Weir for looping @Irfan_Khan in. The video you sent was incredible! I can’t believe how fast it was to return the results!

This gives me a lot of inspiration so thanks for sharing.

2 Likes

You’re welcome. The key to the speedier display is that I limited results (I left it at 10 but you can go much higher) and (most importantly) the results are pasted in bulk as an array. If you don’t bilk process they are pasted in line by line and that will run very slowly.

2 Likes

@Irfan_Khan,

thanks for sharing this most useful technique

unfortunately the link to the document you posted is resulting in an “embed not found” error

can you resolve that?

max

The video isn’t working?

1 Like

The doc needs to be set to public I think. Cool solution!

1 Like

Whoops. Ok hopefully fixed now.

tho again the demo example here wont actually function since its not the exact same DocID for my integration

1 Like

Sorry @David_Weitzman , I completely missed this message! This is really cool and I hope to study the code so that I can create a Google Sheets pack.

1 Like

Thanks so much for sharing, @Irfan_Khan , I found your solution in using webhooks to use GoogleSheets as the back-end very inspiring!

2 Likes

Hey @Irfan_Khan, would you be up to re-recording that video as a loom? The quality would be higher and perhaps you could offer an explanation of what it’s doing

Seems like a very cool solution

2 Likes

I figured this out so thought I would share a bit more detail based on the Make.com scenario I setup.

Step 1 - Setup a Coda Text control with a button next to it that uses the Make.com Webhook pack

image

Step 2 - Create a webhook step in Make and determine the data structure

Step 3 - Add a module to search Google Sheets based on whatever criteria you need

Step 4 (Optional) - Add a router that checks whether results are found or not

image

Step 5 - Use the Text Aggregator module to format the rows that are returned in Google Sheets to the JSON format for rows in the Coda API

Things to note:

  • Row separator should be comma
  • The format should be per the Coda API. Example:

{"cells":[{"column":"*CodaColumnID*","value":"{{*Make.comModuleResult*}}"},{"column":"*CodaColumnID*","value":"{{*Make.comModuleResult*}}"}]}

To get your column ID, see below:

NB: Always use IDs as names are prone to change

Step 6 - Add a Coda Make API call module and construct the URL

To get your doc ID, see here

To get your table ID, see below:

Step 7 - Construct the request body

To explain a bit further:

  • Coda is expecting a JSON formatted request.
  • The previous text aggregator step just combined lots of rows together like this {rowdetails},{rowdetails} etc. so they need to be wrapped in a rows array so that you get “rows”:[{rowdetails},{rowdetails}]
  • You can optionally add a key column so that you can upsert data.
  • Both the rows array and key columns array are wrapped in { and } so that the request is properly formatted.
4 Likes