Note: 

  • Use and transfer of information received from Google APIs to any other app will adhere to Google API Services User Data Policy, including the Limited Use requirements.

  • In accordance with the Nintex Privacy Policy, Nintex Services do not retain any information obtained from third party services.

Google Sheets - Get cell data

Use the Google Sheets - Get cell data action A tool for building the processes, logic, and direction within workflows. Actions are the steps the workflow performs to complete a process. to get rows of data from a specific spreadsheet on Google Drive.

For more information about the Google Sheets connector, go to Google Sheets.

Before you begin: 

Jump to:

Configure the Google Sheets - Get cell data action

  1. Add the action to the workflow and open the action configuration panel. For more information, see Add an action to the workflow.

  1. Select a Connection. If you do not have a connection, see Add a connection.
  2. Select the Spreadsheet you want to get the data from.
  3. Specify the Range of the data you want to retrieve.
  4. Create or select a variable for the Cell data output.

For more information on all the required and optional configuration fields, go to Google Sheets - Get cell data action fields, buttons, and settings.

Google Sheets - Get cell data action fields, buttons, and settings

Section

Field

Description

Variable types
(unlabeled)

Connection Credentials and other settings that enables workflows to communicate with external systems, services, or applications. It's required to pass data between the workflow and the indicated service, which could be a cloud service, business application, or content store.

The Google Sheets connection to use for retrieving the data from the spreadsheet.

To refresh the available connections, click Refresh.

(n/a)
 

Spreadsheet

The spreadsheet to retrieve data from. When you specify the Google Sheets connection, all the spreadsheets in the selected connection are available to select from in the drop-down list. If you want to select a spreadsheet that is not in the list, you can insert a variable in the Spreadsheet field. When using a variable, use the spreadsheet ID. A spreadsheet ID can be extracted from its URL. For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc123 is "abc123".

Text, Decimal, Integer, Boolean, DateTime, Collection
 

Range

The area of the spreadsheet containing the cells to get data from. This includes the sheet ID value and cell values using A1 notation.

A1 notation

This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:

  • Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.

  • Sheet1!A:A refers to all the cells in the first column of Sheet1.

  • Sheet1!1:2 refers to all the cells in the first two rows of Sheet1.

  • Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.

  • A1:B2 refers to the first two cells in the top two rows of the first visible sheet.

  • Sheet1 refers to all the cells in Sheet1.

Named ranges are also supported. When a named range conflicts with a sheet's name, the named range is preferred.

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g., 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

Text, Decimal, Integer, Boolean, DateTime, Collection
 

Display values as

How values should be represented in the output.

  • Formatted: Values will be calculated & formatted in the output according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23".

  • Unformatted: Values will be calculated, but not formatted in the output. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

  • Formula: Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".

Text, Decimal, Integer, Boolean, DateTime, Collection
 

Display dates and times as

How dates and time values should be represented in the output.

  • Serial number: Sets date, time, datetime, and duration fields to output as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day. For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30st 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year.

  • Formatted: Sets date, time, datetime, and duration fields to output as strings in their given number format (which is dependent on the spreadsheet locale).

Text, Decimal, Integer, Boolean, DateTime, Collection

Output

Cell data

Stores the result of the action as an object with the following variables:

  • Range: (Text) The range the values cover, in A1 notation.

  • Dimension: (Text) This action always returns data using Rows.

  • Data: (Collection) The data that was retrieved. This is an array of arrays, the outer array representing all the data and each inner array representing a major dimension (row). Each item in the inner array corresponds with one cell.

 Object