Google Sheets — Used range
Google Sheets — Used range
Section titled “Google Sheets — Used range”Type: googlesheets-get-used-range · Package: Google Sheets Activities v1.0.5 · Output: Dictionary<string,object>
Returns the size of the area with data in a sheet: how many rows and columns, where they end and the range in A1 notation. Useful to know the size before processing, to write right below the last row or to build a dynamic range.
How it works
Section titled “How it works”Reads the sheet sheet (the first one if empty) and measures the area from A1 to the last row with data and the right-most column with data. Empty rows in between count (the area reaches the last value); trailing ones do not.
Required parameters
Section titled “Required parameters”None.
Optional parameters
Section titled “Optional parameters”| Parameter | Type | Default | Description |
|---|---|---|---|
session | ZoanGoogleSheetsSession | — | Session from Google Sheets — Open. Inside Open’s body it is inherited and you can leave it empty. |
sheet | string | — | Sheet name. If empty, the first sheet is used. |
Output
Section titled “Output”Returns a Dictionary with these keys:
| Key | Type | Content |
|---|---|---|
rows | int | Number of rows in the area, counting from row 1. |
columns | int | Number of columns in the area, counting from column A. |
lastRow | int | Number of the last row with data (same as rows). |
lastColumn | int | Number of the last column with data (same as columns; 3 = column C). |
range | string | Full range with the sheet name, e.g. Orders!A1:E120. |
If the sheet is empty, every number is 0 and range is null.
Common errors
Section titled “Common errors”| Message | Cause and fix |
|---|---|
No Google Sheets session available. Provide 'session' from a googlesheets-open activity, or place this activity inside the Open activity's body … | The activity is outside Open’s body and received no session. Move it inside the body or pass the saved session. |
The spreadsheet has no sheets to read. | sheet is empty and the document has no sheets. |
The service sheets has thrown an exception. HttpStatusCode is … | Google rejected the call: missing sheet or range, no access, expired token or quota exceeded. See Google API errors. |
Example
Section titled “Example”Write a total right below the data:
Google Sheets — Used range session = book sheet = "Orders" → output: dimGoogle Sheets — Write cell session = book sheet = "Orders" cell = "E" + (num(dim["lastRow"]) + 1) value = "=SUM(E2:E" + dim["lastRow"] + ")"Related activities
Section titled “Related activities”- Read range — read the area with data.
- Append rows — append rows at the end without working out the position.
- Get sheet names — list the sheets.