This topic was automatically generated from Slack. You can find the original thread here.
Can you explain how to use a custom code step to add multiple rows of data to a google sheet? The docs say ‘The most common pattern is to reference an array of arrays exported by a previous step (e.g., {{steps.foo.$return_value}})’ but I’m unclear how to actually implement that. Ty
Sure, I can help you with that. To add multiple rows of data to a Google Sheet using a custom code step, you’ll need to use the Google Sheets API. First, make sure you have connected your Google account to Pipedream. Here’s an example Pipedream component that appends multiple rows to a Google Sheet: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", }, sheetName: { type: "string", label: "Sheet Name", }, data: { type: "string[]", label: "Data", description: "Enter each row as a JSON array of values, e.g. [\"A1\", \"B1\"]", }, }, async run({ steps, $ }) { const parsedData = this.data.map((row) => JSON.parse(row)); const range = `${this.sheetName}!A1`; const url = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${range}:append`; return await axios($, { method: "POST", url, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: { range, majorDimension: "ROWS", values: parsedData, }, params: { valueInputOption: "RAW", insertDataOption: "INSERT_ROWS", }, }); }, }); In this example, you’ll need to provide the spreadsheetId, sheetName, and data as input props. The data prop should be an array of strings, where each string is a JSON array of values representing a row in the sheet. For example, if you want to add two rows with values “A1”, “B1” and “A2”, “B2”, you would enter: ["[\"A1\", \"B1\"]", "[\"A2\", \"B2\"]"] This component will append the rows to the specified sheet using the Google Sheets API. Make sure to replace the spreadsheetId and sheetName with the appropriate values for your Google Sheet.
does each inner array need to be a string like it is in your example?
my 2D array looks like this [
[ ‘293110566’ ], [ ‘291909834’ ],
[ ‘291909785’ ], [ ‘290980686’ ],
[ ‘291768728’ ], [ ‘291768761’ ],
[ ‘291768721’ ], [ ‘291768655’ ],
[ ‘292518506’ ], [ ‘291542592’ ],
[ ‘291768651’ ], [ ‘291768716’ ],
[ ‘291768647’ ], [ ‘291768710’ ],
[ ‘291768644’ ], [ ‘291768703’ ],
[ ‘291768639’ ], [ ‘291052525’ ],
[ ‘291768693’ ], [ ‘291768625’ ]
]
Apologies for the confusion in my previous example. The inner arrays do not need to be strings. You can directly use arrays of values. Here’s an updated example: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", }, sheetName: { type: "string", label: "Sheet Name", }, data: { type: "array", label: "Data", description: "Enter each row as an array of values, e.g. [\"A1\", \"B1\"]", }, }, async run({ steps, $ }) { const range = `${this.sheetName}!A1`; const url = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${range}:append`; return await axios($, { method: "POST", url, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: { range, majorDimension: "ROWS", values: this.data, }, params: { valueInputOption: "RAW", insertDataOption: "INSERT_ROWS", }, }); }, }); In this updated example, the data prop has a type of array. You can directly input arrays of values for each row, like ["A1", "B1"] and ["A2", "B2"].
I’m mapping into a single column only, do I need to include the column letter?
I’m mapping into a single column only, do I need to include the column letter? It looks like in your example, you hardcode to the range A1. What if I want to provide my own range? In this case, the range will be a column
If you want to provide your own range and append data to a specific column, you can modify the range in the code accordingly. For example, if you want to append data to column B, you can set the range to B1. Here’s the updated code with a custom range: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", }, sheetName: { type: "string", label: "Sheet Name", }, data: { type: "array", label: "Data", description: "Enter each row as an array of values, e.g. [\"A1\", \"B1\"]", }, customRange: { type: "string", label: "Custom Range", description: "Enter the custom range, e.g. B1", }, }, async run({ steps, $ }) { const range = `${this.sheetName}!${this.customRange}`; const url = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${range}:append`; return await axios($, { method: "POST", url, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: { range, majorDimension: "ROWS", values: this.data, }, params: { valueInputOption: "RAW", insertDataOption: "INSERT_ROWS", }, }); }, }); In this updated example, I’ve added a new prop called customRange. You can input your desired range, like “B1”, and the code will append data to that specific column. Make sure to replace the spreadsheetId, sheetName, and data with the appropriate values for your Google Sheet.
Got it, ty!
How do I paginate through graphQL query data in Pipedream? For ex. my query returns true for the field hasNextPage and I can currently only parse the first 20 of 24 results. How do I access the remaining 4 results? Do I need to add a custom code step that checks whether the hasNextPage field is true and if it is, requery the api?
To paginate through GraphQL query data in Pipedream, you can indeed add a custom code step that checks whether the hasNextPage field is true and, if it is, requery the API. You’ll need to use the appropriate cursor or pagination parameter in your GraphQL query to fetch the next set of results. Here’s an example of how you can achieve this using a while loop: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { graphqlEndpoint: { type: "string", label: "GraphQL Endpoint", }, query: { type: "string", label: "GraphQL Query", }, }, async run({ steps, $ }) { let hasNextPage = true; let cursor = null; let allResults = []; while (hasNextPage) { const response = await axios($, { method: "POST", url: this.graphqlEndpoint, headers: { "Content-Type": "application/json", }, data: { query: this.query, variables: { cursor: cursor, }, }, }); const data = response.data; // Replace this with the correct path to the data in the response const pageInfo = data.pageInfo; // Replace this with the correct path to the pageInfo in the response hasNextPage = pageInfo.hasNextPage; cursor = pageInfo.endCursor; // Replace this with the correct path to the endCursor in the response allResults.push(...data.results); // Replace this with the correct path to the results in the response } return allResults; }, }); In this example, replace this.graphqlEndpoint and this.query with the appropriate values for your GraphQL API. Also, update the paths to the data, pageInfo, hasNextPage, endCursor, and results based on the structure of the GraphQL response you receive. This code will keep fetching results as long as hasNextPage is true and update the cursor for each subsequent request. The results will be accumulated in the allResults array, which will be returned at the end.
Aren’t there GraphQL client libraries I can use in Node.js that will handle all this for me?
Yes, there are GraphQL client libraries available for Node.js that can help you handle pagination and other tasks more easily. One popular library is graphql-request. You can use this library in a Pipedream custom code step by installing it as a dependency. First, add the graphql-request package to your workflow by clicking on the “Add npm package” button at the bottom of the code editor and searching for “graphql-request”. Once it’s added, you can use it in your custom code step like this: import { GraphQLClient } from "graphql-request"; export default defineComponent({ props: { graphqlEndpoint: { type: "string", label: "GraphQL Endpoint", }, query: { type: "string", label: "GraphQL Query", }, }, async run() { const client = new GraphQLClient(this.graphqlEndpoint); let hasNextPage = true; let cursor = null; let allResults = []; while (hasNextPage) { const variables = { cursor: cursor, }; const response = await client.request(this.query, variables); const data = response.data; // Replace this with the correct path to the data in the response const pageInfo = data.pageInfo; // Replace this with the correct path to the pageInfo in the response hasNextPage = pageInfo.hasNextPage; cursor = pageInfo.endCursor; // Replace this with the correct path to the endCursor in the response allResults.push(...data.results); // Replace this with the correct path to the results in the response } return allResults; }, }); Replace this.graphqlEndpoint and this.query with the appropriate values for your GraphQL API. Also, update the paths to the data, pageInfo, hasNextPage, endCursor, and results based on the structure of the GraphQL response you receive. This code will handle pagination using the graphql-request library, making it easier to fetch all the results.