Google Sheets

Get Cell Values from Spreadsheet

Retrieve cell values from a Google Sheets spreadsheet.

Access Spreadsheet

Before using this node, you must ensure the spreadsheet is shared with public access for this node to function properly.

If it does not appear as shown above, you can enable public access as follows:

Spreadsheet ID

The ID of the Google Sheet. You can copy the entire sheet URL and paste it into the node, which will automatically extract the ID and discard the remaining characters.

Range

The range of cells, you want to retrieve, update, or delete. You can specify the range using A1 notation like Sheet1!A1:B2 or A1:B2, or R1C1 notation like Sheet1!R1C1:R2C2 or R1C1:R2C2.

Reference Key

The reference key to identify data from Google Sheets.

Use First Row as Key

When selected, the first row of the spreadsheet is used as keys, allowing you to reference column values using these keys.

Primary Key Column Name

To use data specific to the running profile, select this option.

Values as Array

To use multiple data entries for the same profile, select this option.

Preview Data

If you directly enter the ID into the Spreadsheet ID field, you can preview the data here.

General Expression

The general expression to retrieve data from Google Sheets is {{googleSheets.reference_key}}.

If the Primary Key Column Name is not configured, the returned value will be an array. To access the first element, use the general expression {{googleSheets.reference_key.0}}.

Example

For example, the data in the Google Sheets node is as follows:

Use {{googleSheets.data.0}} to access the first element and assign it to variable a

To access the link value in that element, use {{googleSheets.data.0.link}}. When assigned to variable b, the value will be:

Practical Examples

Example: Log in to a Specific Profile

Example

Here you want to use data from Google Sheets to log in to accounts on w3schools.

First, you create a sheet with columns profileId, email, and pass:

Next, you configure the Google Sheets node to retrieve data from the sheet. Paste the Spreadsheet ID into the Spreadsheet ID field. For the range, you set the data range to A1:C3, or to be safe, A1:C1000 to account for additional data. Set the reference key to data. Then you select Use First Row as Key to use the column names as keys. Next you choose the primary key column as profileId to match data rows to the profile in use.

Configure a Key Press node to enter the email from the email column in Google Sheets. Specify the selector for the email input field. Next you select the Type Content option and use the expression to retrieve data from rows matching the running profile’s profileId. For example, if the profile ID is 21961, the email is retrieved using the general expression {{googleSheets.reference_key.[profileId].column_name}}, which in this case is {{googleSheets.data.[profileId].email}}, resulting in [email protected].

Next, you configure another Key Press node to input the password from the pass column in Google Sheets. You enter the selector for the password input element, select the Type Content option, and use the expression to retrieve data from rows matching the running profile’s profileId. For example, if the profile ID is 21961, the password is retrieved using {{googleSheets.data.[profileId].pass}}, resulting in ioqwe812hed.

Finally, after entering the information, you use a Click element node configured as follows to click the Login button:

The node cluster will look like this:

When running this node cluster, the workflow enters the email and password and clicks the Login button as desired:

Example: Using Multiple Data Entries for One Profile

Example

Here you want to open multiple YouTube video links and perform the action of scroll each video.

First, you prepare a sheet with two columns: profileId and link:

Next, you configure the Google Sheets node to retrieve data from the sheet. You paste the Spreadsheet ID into the Spreadsheet ID field. For the range, you set the data range to A1:B3, or to be safe, A1:B1000 to account for additional data. Set the reference key to data. Then you select Use First Row as Key to use the first row as keys. You choose the primary key column as profileId to match data rows to the profile in use.

Next, you select Values as Array. This makes the data for each profileId an array of values:

Then, you use the Insert Data node to assign the array of values for each corresponding profileId to a variable arr:

Next, you configure the Loop Data node to iterate through the arr variable:

Then, you configure the Open URL node to use the value from each iteration of the Loop Data node in the New Tab URL field. Instead of a regular URL, you use the expression to retrieve the value for each iteration: {{loopData.arr.link}}.

Next, you configure a Scroll element node to scroll the newly opened video:

Then, you use a Pause node to wait 1 second before opening the next link.

Finally, you use a Stop Loop node to mark the end of the loop.

The node cluster will look like this:

When running, the workflow opens the links corresponding to the profileId and scrolls each video.

Note

If this appears, it means Google Sheets is blocking data retrieval. Wait a while and try again later: