This topic was automatically generated from Slack. You can find the original thread here.
How can I set a Node.js step that will search a value in a specific row in google sheets?
To search for a value in a specific row in Google Sheets using a Node.js step, you can use the Google Sheets API. First, you’ll need to add the Google Sheets app to your workflow and connect your Google account. Here’s an example Pipedream component that searches for a value in a specific row of 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", }, rowNumber: { type: "integer", label: "Row Number", }, searchValue: { type: "string", label: "Search Value", }, }, async run({ $ }) { const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${this.sheetName}!A${this.rowNumber}:Z${this.rowNumber}`; const response = await axios($, { url: apiUrl, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); const rowData = response.data.values[0]; const foundIndex = rowData.findIndex((cellValue) => cellValue === this.searchValue); if (foundIndex !== -1) { return { found: true, index: foundIndex, value: this.searchValue }; } else { return { found: false, value: this.searchValue }; } }, }); Replace the spreadsheetId, sheetName, rowNumber, and searchValue props with the appropriate values for your use case. This component will search for the specified value in the given row and return an object indicating whether the value was found, the index of the found value, and the searched value.
I need to search in a specific column
To search for a value in a specific column in Google Sheets using a Node.js step, you can use the Google Sheets API. First, you’ll need to add the Google Sheets app to your workflow and connect your Google account. Here’s an example Pipedream component that searches for a value in a specific column of 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", }, columnLetter: { type: "string", label: "Column Letter", }, searchValue: { type: "string", label: "Search Value", }, }, async run({ $ }) { const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${this.sheetName}!${this.columnLetter}:${this.columnLetter}`; const response = await axios($, { url: apiUrl, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); const columnData = response.data.values.map((row) => row[0]); const foundIndex = columnData.findIndex((cellValue) => cellValue === this.searchValue); if (foundIndex !== -1) { return { found: true, index: foundIndex, value: this.searchValue }; } else { return { found: false, value: this.searchValue }; } }, }); Replace the spreadsheetId, sheetName, columnLetter, and searchValue props with the appropriate values for your use case. This component will search for the specified value in the given column and return an object indicating whether the value was found, the index of the found value, and the searched value.
I’m gettin this error Cannot read properties of undefined (reading 'values')
I apologize for the issue you’re facing. It seems that the error is caused by the response.data.values being undefined. To handle this case, you can add a check for the existence of response.data.values before accessing its elements. Here’s the updated code for searching in a specific column: 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", }, columnLetter: { type: "string", label: "Column Letter", }, searchValue: { type: "string", label: "Search Value", }, }, async run({ $ }) { const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${this.sheetName}!${this.columnLetter}:${this.columnLetter}`; const response = await axios($, { url: apiUrl, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); if (response.data.values) { const columnData = response.data.values.map((row) => row[0]); const foundIndex = columnData.findIndex((cellValue) => cellValue === this.searchValue); if (foundIndex !== -1) { return { found: true, index: foundIndex, value: this.searchValue }; } else { return { found: false, value: this.searchValue }; } } else { return { error: "No data found in the specified column", value: this.searchValue }; } }, }); This code now checks for the existence of response.data.values before processing the data. If no data is found in the specified column, it will return an error message.
This code isn’t working, can you fix?
import { axios } from “@pipedream/platform”
export default defineComponent({
props: {
google_sheets: {
type: “app”,
app: “google_sheets”,
}
},
async run({steps, $}) {
return await axios($, {
method: “PUT”,
url: https://sheets.googleapis.com/v4/spreadsheets/1r4QyBktB-LtRna3BWIiSFr-uQcWQbYL-3S6sIGD0xgs/values/Purchases!A3:D3?valueInputOption=RAW,
headers: {
Authorization: Bearer ${**this**.google_sheets.$auth.oauth_access_token},
},
data: {
“range”: “Purchases!A3:D3",
“majorDimension”: “ROWS”,
“values”: [
[“Updated”, “Row”, “With”, “Data”],
[“Another”, “Updated”, “Row”]
]
},
})
},
})
I apologize for the issue you’re facing. It seems there are some syntax issues in the code. I’ve corrected the code for you: ```
import { axios } from “@pipedream/platform”; export default defineComponent({ props: { google_sheets: { type: “app”, app: “google_sheets”, }, }, async run({ steps, $ }) { return await axios($, { method: “PUT”, url: https://sheets.googleapis.com/v4/spreadsheets/1r4QyBktB-LtRna3BWIiSFr-uQcWQbYL-3S6sIGD0xgs/values/Purchases!A3:D3?valueInputOption=RAW, headers: { Authorization: Bearer ${this.google_sheets.$auth.oauth_access_token}, }, data: { range: “Purchases!A3:D3”, majorDimension: “ROWS”, values: [ [“Updated”, “Row”, “With”, “Data”], [“Another”, “Updated”, “Row”], ], }, }); }, });
I’m getting this error
{"error":{"code":400,"message":"Requested writing within range [Purchases!A3:F3], but tried writing to row [4]","status":"INVALID_ARGUMENT"}}
I apologize for the error you’re encountering. It seems that the issue is related to the specified range in your request. You might be trying to write data outside the specified range. To help you further, can you please provide the code you’re using to make this request? This will help me understand the issue better and provide a solution.
How can I calculate the exact memory a Node.js step consuming?
To calculate the exact memory consumption of a Node.js step in a Pipedream workflow, you can use the process.memoryUsage() function, which returns an object with memory usage information. Here’s an example of how to use it in a Pipedream component: ```
export default defineComponent({ async run({ steps, $ }) { const memoryUsageBefore = process.memoryUsage(); // Your code here const memoryUsageAfter = process.memoryUsage(); const memoryDifference = { rss: memoryUsageAfter.rss - memoryUsageBefore.rss, heapTotal: memoryUsageAfter.heapTotal - memoryUsageBefore.heapTotal, heapUsed: memoryUsageAfter.heapUsed - memoryUsageBefore.heapUsed, external: memoryUsageAfter.external - memoryUsageBefore.external, }; console.log(“Memory usage difference:”, memoryDifference); return memoryDifference; }, });