How to dynamically retrieve all worksheets in a Google Sheet using user input?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

Hi Im trying to allow user input through a google sheet to deploy data. is there a way to dynamically get all of the worksheets in one step?

I don’t see it as an option. can the expression that specifies the worksheet return a list of all worksheets… Let me know if anyon has solved this one my limited google foo has me lost migt be my searchs.

2

Hi Kier, I’m going to see how our new Q&A Bot Pi responds to this

3

Hi Im trying to allow user input through a google sheet to deploy data. is there a way to dynamically get all of the worksheets in one step?

I don’t see it as an option. can the expression that specifies the worksheet return a list of all worksheets… Let me know if anyon has solved this one my limited google foo has me lost migt be my searchs.

4

To get all the worksheets in a Google Sheet, you can use the Google Sheets API. Here’s an example Pipedream component that retrieves all the worksheets in a specific Google Sheet: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, sheetId: { type: "string", label: "Google Sheet ID", }, }, async run({ steps, $ }) { const response = await axios($, { url: `https://sheets.googleapis.com/v4/spreadsheets/${this.sheetId}`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); const worksheets = response.data.sheets.map((sheet) => sheet.properties.title); return worksheets; }, }); Replace sheetId with the ID of the Google Sheet you want to get the worksheets from. This component will return a list of all worksheet names in the specified Google Sheet.

5

Let me know if that helps. That code should give you a list of all spreadsheets that you can use in future steps. If that’s not what you’re looking for, try asking Pi by mentioning it like I did above. I’ll also monitor its output and suggest things it misses.