I’m trying to figure out if it’s possible to use input data from an upstream trigger or action to specify the Google Sheets Spreadsheet or Worksheet name to use. The trigger for this zap is a webhook from Google Sheets that includes a variable called “id”. I would like to use the contents of that variable to determine which Spreadsheet or Worksheet should be written to with the output of the zap.
Best answer
Using input data as Google Sheets Spreadsheet or Worksheet name
- Beginner
- 1 reply
Best answer by nicksimard
Hi
We have an in-depth, step-by-step, article about dynamically setting worksheets in Google Sheets:
Troy’s suggestion would work, or if you already know the list of worksheets/instructors you could create a Lookup Table (either using our Formatter by Zapier app or something like Google Sheets).
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.
+8- Zapier Staff
- 1366 replies
Hi
Thanks for reaching out- happy to help!
Am I understanding correctly that you are looking to divvy up the data being pulled in from your webhook to either go to a predetermined spreadsheet or worksheet, using the variable ID?
If that is correct, I think you could achieve this by using Filter by Zapier or Paths by Zapier. Let me know if we’re on the same page or if I’m not completely understanding your workflow and we can dig deeper!
- Beginner
- 1 reply
Hi @Liz_Roberts,
Yes, I think you understand correctly. I have google sheets named sheet_1, sheet_2, sheet_3. I have a google script set up to parse out the number in the name of the sheet (i.e. 1, 2, 3) and send that value as a webhook to zapier. I would like zapier to perform an action, and then send the data back to the appropriate sheet (sheet_1, sheet_2 or sheet_3) using the number received on the webhook.
Can I used Filter or Paths to do this?
Thank you.
+10- Zapier Expert
- 2437 replies
Hi
Sorry, this question got missed somehow. Were you able to get this working?
+8- Zapier Staff
- 1366 replies
Hi
Circling back here to see if you were able to get this squared away! Let us know if we can be of further help with filters or paths!
- Beginner
- 4 replies
I have the same question, but don’t see where this was ever answered. Is it possible?
I have a Cognito Form that collects registration from students. I want to put the information for the student in a google sheet for the appropriate instructor.
For instance if the instructor’s name is “John Doe”, it would right to the spreadsheet named “John Doe”
I know I could hard code it using filters/paths, but it would be a lot simpler to just be able to use the instructor name from the form as a custom value for the spreadsheet name.
+14- Zapier Expert
- 30938 replies
Hi
The GSheet Spreadsheet and Worksheet fields expect the internal GSheet ID, so you can’t dynamically use the Instructors name.
GSheets has factors to be aware of in order to work properly with Zapier so I suggest reviewing the available help articles first: https://zapier.com/apps/google-sheets/help
Try adding a GDrive Find File step before then mapping over the found GSheets ID:
+11- Zapier Staff
- 2115 replies
- Answer
Hi
We have an in-depth, step-by-step, article about dynamically setting worksheets in Google Sheets:
Troy’s suggestion would work, or if you already know the list of worksheets/instructors you could create a Lookup Table (either using our Formatter by Zapier app or something like Google Sheets).
- Beginner
- 4 replies
Thank you! That’s exactly what I’m trying to do. I will give it a try.
1 person likes this
I ended up not using Zapier due to the cost being unreasonable for a personal tool, but hopefully this is useful to you, or someone else. I needed to read sheets by name, but they were all being created manually with dates as their names so I had no way to get their IDs.
I ended up making a sheet at the end of my document named “SheetReference”
The first row had headers named “Sheet Name” and “Sheet ID”, but frankly their names don’t matter.
I went into the google sheets script system and added a function that looks like:
function getSheetInfo() {
var values = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => [
s.getSheetName(), s.getSheetId()
])
SpreadsheetApp.getActive()
.getSheetByName('SheetReference')
.getRange("A2:B" + (values.length + 1))
.setValues(values);
}Run it manually to get the page started, then you can setup a trigger (triggers on the left side of the apps script page) to have this run once a day so it gets updated when sheets are added/removed.
Since the ID for “SheetReference” never changes you can setup a step to read from that document and look up the ID based on the sheet name in the table. Just use normal mechanisms to get the ID for the “SheetReference” sheet (Look in the URL after the gid= to get it - it’ll also be on your new table on that sheet, too, so you can look there after you run it once.)
Then you can use the ID, that you retrieved, in a later google sheets step to read from the appropriate sheet in the same document. In my case, I was able to use a formatted version of the date to use the first lookup step and get an ID, and then access the sheet that had that name in the next step.
1 person likes this
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.