Google Sheets

Get Cell Values from Spreadsheet

Retrieve cell values from a Google Sheets spreadsheet.

Access Spreadsheet

Before using this node, I must ensure the sheet is shared with public access.

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

Spreadsheet ID

The ID of the Google Sheet. I 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 I want to retrieve, update, or delete. I 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 me 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 I directly enter the ID into the Spreadsheet ID field, I 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:

To access the first element, the expression is {{googleSheets.data.0}}. When assigned to variable a, the value will be:

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, I want to use data from Google Sheets to log in to accounts on w3schools.

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

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

Then, I configure a Key Press node to input the email from the email column in Google Sheets. First, I enter the selector for the email input element. Next, I 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, I configure another Key Press node to input the password from the pass column in Google Sheets. I 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, I use a Mouse Click 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, I want to open multiple YouTube video links and perform the action of scroll each video.

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

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

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

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

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

Then, I 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, I use the expression to retrieve the value for each iteration: {{loopData.arr.link}}.

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

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

Finally, I 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 of the running profile and likes each video:

Note

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