This topic was automatically generated from Slack. You can find the original thread here.
If I want to update particular column and row in Google Sheet, which app would I use?
To update a particular column and row in a Google Sheet, you would use the Google Sheets app on Pipedream. Google Sheets is part of the Google suite of products, and Pipedream supports integration with Google Sheets, allowing you to perform various operations on your sheets, including updating specific cells. While the specific details on pre-built actions for updating cells in Google Sheets are not provided in the documentation excerpts available to me, you can accomplish this by using the Google Sheets API directly within a Pipedream workflow. Here’s a general approach on how you could structure a Pipedream component to update a specific cell: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, }, async run({ steps, $ }) { const spreadsheetId = "YOUR_SPREADSHEET_ID"; const range = "Sheet1!A1"; // Specify the sheet name and cell to update, e.g., Sheet1!A1 const valueInputOption = "USER_ENTERED"; // Allows input as if entered directly in the Google Sheets UI const values = [ ["YOUR_NEW_VALUE"], // The new value you want to set for the specified cell ]; const updateResponse = await axios($, { method: "PUT", url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`, params: { valueInputOption, }, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, data: { range, values, }, }); return updateResponse; }, }); Please replace "YOUR_SPREADSHEET_ID" with your actual spreadsheet ID, "Sheet1!A1" with the target sheet name and cell reference, and "YOUR_NEW_VALUE" with the value you wish to set in the specified cell. This code uses the Google Sheets API to update a specific cell in your sheet. Make sure to have the necessary OAuth scopes enabled for your Google Sheets integration on Pipedream. The default scopes provided should suffice for this operation, but you can review and adjust them as needed. For more detailed instructions and other operations you can perform with Google Sheets on Pipedream, please visit the Google Sheets app documentation on Pipedream: [Google
Can you rewrite the following code to work with field inputs?
import { axios } from "@pipedream/platform";
export default defineComponent({
props: {
google: {
type: "app",
app: "google",
},
},
async run({ steps, $ }) {
const spreadsheetId = "YOUR_SPREADSHEET_ID";
const range = "Sheet1!A1"; // Specify the sheet name and cell to update, e.g., Sheet1!A1
const valueInputOption = "USER_ENTERED"; // Allows input as if entered directly in the Google Sheets UI
const values = [
["YOUR_NEW_VALUE"], // The new value you want to set for the specified cell
];
const updateResponse = await axios($, {
method: "PUT",
url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`,
params: {
valueInputOption,
},
headers: {
Authorization: `Bearer ${this.google.$auth.oauth_access_token}`,
},
data: {
range,
values,
},
});
return updateResponse;
},
});
To rewrite the provided code to work with field inputs, we’ll introduce props for spreadsheetId, range, and values. This allows users to input these values directly in the Pipedream UI when configuring the step. Here’s how the updated code looks: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", description: "The ID of the Google Spreadsheet.", }, range: { type: "string", label: "Range", description: "Specify the sheet name and cell to update, e.g., Sheet1!A1", }, values: { type: "string[]", label: "Values", description: "The new values you want to set for the specified cell, in JSON format. E.g., [['YOUR_NEW_VALUE']]", }, }, async run({ steps, $ }) { const valueInputOption = "USER_ENTERED"; // Allows input as if entered directly in the Google Sheets UI const updateResponse = await axios($, { method: "PUT", url: `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${this.range}`, params: { valueInputOption, }, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, data: { range: this.range, values: JSON.parse(this.values), // Parsing the string input to JSON }, }); return updateResponse; }, }); In this updated version, spreadsheetId, range, and values are defined as props, allowing users to input these values in the Pipedream UI. The values prop is expected to be a JSON string (e.g., [["YOUR_NEW_VALUE"]]), which we parse into JSON format with JSON.parse(this.values) before passing it to the API request. This approach makes the component more flexible and user-friendly.
When I am putting in the “YOUR_NEW_VALUE” I have it as [[‘{{steps.create_subfolder.$return_value.id}}’]], however, I am getting the following error:
SyntaxError
Unexpected token ''', "[['1Rk88Rc7-"... is not valid JSON
It looks like you’re trying to use an expression within an array but are encountering a syntax error due to the use of single quotes (') and double square brackets ([[ ]]). The correct way to include an expression within an array in Pipedream is to use double curly braces ({{ }}) without additional quotes or square brackets, unless the specific context requires them. If you’re trying to set YOUR_NEW_VALUE to an array that contains the ID returned from a previous step named create_subfolder, and assuming this ID should be the only element in the array, you should format it like this: on [ "{{steps.create_subfolder.$return_value.id}}" ] This format correctly uses the double curly braces to insert the expression and wraps the expression in square brackets to denote an array with a single element. However, if your specific use case requires a nested array (an array within an array), and that’s why you were using double square brackets, then you should ensure the expression itself is correctly formatted without the single quotes, like so: ```
on [ [“{{steps.create_subfolder.$return_value.id}}”] ]