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:
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
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 . . .
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
@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:
SEARCH WONT WORK IN THIS SAMPLE!
edit: ive been pretty busy with work and have vacation soon so apologies if im slow to reply
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.
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.
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?
The doc needs to be set to public I think. Cool solution!
Whoops. Ok hopefully fixed now.
tho again the demo example here wont actually function since its not the exact same DocID for my integration
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.
Thanks so much for sharing, @Irfan_Khan , I found your solution in using webhooks to use GoogleSheets as the back-end very inspiring!
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
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
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
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:
{"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:
