Tasks

Google Sheet - Get spreadsheet metadata

This task retrieves metadata about a Google Spreadsheet and its sheets.

This task fetches comprehensive metadata about a spreadsheet including its properties and information about all sheets within it (titles, IDs, row/column counts, etc.).

There are two options for Stubber to gain access to a google sheet:

1. Share your sheet with the following stubber account: share-with@stubber-sheets.iam.gserviceaccount.com
2. Create a credential in Stubber Manage

The rest of this document assumes familiarity with adding a task to a stub. See tasks documentation if you need a refresher.

See the Using Google Sheets Guide for more information on how to use Google Sheets with Stubber.

# Basic usage

Retrieve metadata for a spreadsheet by providing only the spreadsheet ID and service account.

# Result

# Parameters

# spreadsheet_id

required

string

This is the unique id of the Google Sheet on Google's system. It can be found in the url of a spreadsheet, as can be seen here:

https://docs.google.com/spreadsheets/d/1ZaYk1_2-o9aefRrd4jdEFgYO0ED5hOuPlE3qMitY/

The string in bold, 1ZaYk1_2-o9aefRrd4jdEFgYO0ED5hOuPlE3qMitY, is the spreadsheet_id

Default: null

# service_account

optional

string

The email address of the service account to use to access the Google Sheet. This is the recommended method of access control for Google Sheets (as opposed to using the orgcredentialuuid parameter).

The available Stubber service accounts:

• share-with@stubber-sheets.iam.gserviceaccount.com (read, write)

Default: null

# orgcredentialuuid

optional

string

This is a unique identifier for the org credential that Stubber should use to access the specified spreadsheet. It is recommended to use the service account instead of this method.

Default: null

# Result

# Properties

# message

The response message after successfully retrieving the spreadsheet metadata.

# success

Either true or false based on the outcome.

# payload.spreadsheet

An object containing spreadsheet-level metadata:

• spreadsheet_id - The unique identifier for the spreadsheet
• spreadsheet_title - The title of the spreadsheet
• spreadsheet_url - The direct URL to access the spreadsheet in Google Sheets
• locale - The locale/language setting of the spreadsheet (e.g., "en_US")
• time_zone - The timezone setting of the spreadsheet (e.g., "Etc/GMT")
• auto_recalc - When formulas are recalculated (e.g., "ON_CHANGE")
• sheet_count - The total number of sheets in the spreadsheet

# payload.sheets

An array of objects, each containing metadata about an individual sheet:

• sheet_id - The unique identifier for the sheet
• title - The name/title of the sheet tab
• index - The position of the sheet (0-indexed, leftmost sheet is 0)
• sheet_type - The type of sheet (typically "GRID" for standard spreadsheets)
• row_count - The total number of rows in the sheet
• column_count - The total number of columns in the sheet

# Use cases

This task is useful for:

1. Dynamic sheet selection - Retrieve all available sheet titles to present to users or to validate that a specific sheet exists before performing operations on it.

2. Sheet discovery - When building integrations, discover what sheets are available in a spreadsheet without manually checking.

3. Validation - Verify that a spreadsheet has the expected structure (correct sheets, proper naming) before performing data operations.

4. Monitoring - Track changes to spreadsheet structure over time by periodically fetching metadata.

# Example with credential

Using an organization credential instead of the service account:

        {
    "tasktype": "get_spreadsheet_metadata",
    "details": {
        "orgcredentialuuid": "your-credential-uuid-here"
    },
    "params": {
        "spreadsheet_id": "~~stub.data.spreadsheet_id"
    },
}

      

This returns the same metadata structure, but uses OAuth2 authentication via the provided credential instead of the service account.