How to Update Multiple Values in Google Sheets Using Python Node Output?

Help
1

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

My goal is to update an arbitrary number of values in a google sheets from information derived from a python node.

Example of the data being exported in python node:

{"1":{"A":"Name 1","B":"Note 1","C":"Code 1","D":154478},"2":{"A":"Name 2","B":"Note 2","C":"Code 2","D":54689},"3":{"A":"Name 3","B":"Note 3","C":"Code 3","D":12},"4":{"A":"Name 4","B":"Note 4","C":"Code 4","D":9999}, . . . . . }

Spreadsheet column set up:

A1 = "Name", B1 = "Icon", C1 = "Code", D = "No.Value"
A2 = "Name 1", B2 = "Icon 1", C2 = "Code 1", D = "154478"
A3 = "Name 2", B3 = "Icon 2", C3 = "Code 2", D = "54689"
A4 = "Name 3", B4 = "Icon 3", C4 = "Code 3", D = "888"

I have a Python node that outputs an array of arrays which I would like to use to update values or insert new rows into a Google sheet. The Google sheets Upsert row seems to only take, and update one value at a time (which I can’t seem to pass an array of arrays to iterate through). As a concrete example, with the given data above and the images below, cell D4 would need to be updated from 888 to 12 and a new row inserted as “Code 4” does not appear in Column C (key column).

How would I set up my upsert node to fulfil this requirement, or would I have to create a new Python node to accomplish this? If the latter, what code would I need to run to do so?

Thanks in advance!

image.png
image.png647×734 14.3 KB

image.png

2

To update multiple values in a Google Sheet using the data from your Python node, you can use a code step in your Pipedream workflow. First, you’ll need to add a Google Sheets action to your workflow to get the required authentication. Then, you can use the Google Sheets API in a Node.js code step to update the values. Here’s an example of how you can set up a Node.js code step to update the Google Sheet: 1. Add a Google Sheets action (e.g., “Get Sheet”) to your workflow to get the required authentication. 2. Add a Node.js code step after your Python step and the Google Sheets action. 3. Use the following code in the Node.js code step: import { axios } from "@pipedream/platform"; // Replace with your Google Sheet ID and range const sheetId = "YOUR_SHEET_ID"; const range = "Sheet1!A1:D"; // Get the data from the previous Python step const data = steps.python_step_name.$return_value; // Prepare the data for the Google Sheets API const values = Object.values(data).map((row) => Object.values(row)); // Call the Google Sheets API to update the values const response = await axios(this.google.$auth, { method: "PUT", url: `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${range}`, headers: { "Content-Type": "application/json", }, params: { valueInputOption: "RAW", }, data: { range, values, }, }); return response; Make sure to replace YOUR_SHEET_ID with your Google Sheet ID and steps.python_step_name with the name of your Python step. Also, adjust the range variable to match the range of cells you want to update in your Google Sheet. This code will update the specified range in your Google Sheet with the data from your Python step. If you need to insert new rows or perform more complex operations, you can refer to the Google Sheets API documentation for more details on how to modify the API calls.

3

Could you provide a code example with Python code instead?

4

Sure, here’s an example of how you can update the Google Sheet using Python code within a Pipedream workflow. First, add a “Run Python Code” action to your workflow. Then, use the following Python code in the action: import json import requests # Replace with your Google Sheet ID and range sheet_id = "YOUR_SHEET_ID" range_ = "Sheet1!A1:D" # Get the data from the previous Python step data = pd.steps.python_step_name.return_value # Prepare the data for the Google Sheets API values = [list(row.values()) for row in data.values()] # Call the Google Sheets API to update the values url = f"https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/{range_}" headers = { "Content-Type": "application/json", "Authorization": f"Bearer {pd.google.$auth.oauth_access_token}", } params = { "valueInputOption": "RAW", } body = { "range": range_, "values": values, } response = requests.put(url, headers=headers, params=params, data=json.dumps(body)) response.raise_for_status() return response.json() Make sure to replace YOUR_SHEET_ID with your Google Sheet ID and pd.steps.python_step_name with the name of your Python step. Also, adjust the range_ variable to match the range of cells you want to update in your Google Sheet. This code will update the specified range in your Google Sheet with the data from your Python step. If you need to insert new rows or perform more complex operations, you can refer to the Google Sheets API documentation for more details on how to modify the API calls.