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

3 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