Google Sheets
The Google Sheets action allows you to interact with Google Sheets. It supports creating new spreadsheets, appending data to existing sheets, and exporting spreadsheets in various formats. Mechanic interacts with Google Sheets via the Google Sheets API, using OAuth2 for authentication.
Options
account
string
Required: the Google account email address to authenticate with
operation
string
Required: the operation to perform. One of: "append_rows", "create_spreadsheet", "export_spreadsheet"
spreadsheet_id
string
Required: for append_rows and export_spreadsheet; the ID of the target spreadsheet
title
string
Required: for create_spreadsheet; the title for the new spreadsheet
rows
array
Required: for append_rows and optional for create_spreadsheet; array of arrays containing the data to write
sheet_name
string
Optional: for append_rows; defaults to "Sheet1"
file_type
string
Optional: for export_spreadsheet; the format to export. One of: "xlsx" (default), "csv", "pdf", "html", "ods", "tsv"
folder_path
string
Optional: for create_spreadsheet; the folder path where the spreadsheet should be created (e.g., "reports/2024/monthly")
Operations
append_rows
Adds new rows to an existing spreadsheet.
Required Options
account
spreadsheet_id
rows
Optional Options
sheet_name (defaults to "Sheet1")
create_spreadsheet
Creates a new spreadsheet, optionally with initial data.
Required Options
account
title
Optional Options
folder_path (path where spreadsheet should be created)
rows (initial data to populate the spreadsheet)
export_spreadsheet
Exports a spreadsheet in various formats.
Required Options
account
spreadsheet_id
Optional Options
file_type
xlsx (default)
csv
pdf
html
ods
tsv
Authentication
This action requires connecting a Google account with the appropriate permissions. To connect an account:
Go to the Settings screen
Click Authentication
Follow the Google account connection flow
File Access
The action can only access spreadsheets it creates, no other spreadsheets in your drive.
Folder Support
When creating spreadsheets, you can specify a folder path to organize your files:
Use forward slashes to separate folder names (e.g., "reports/2024/monthly")
Folders will be created if they don't exist
Can only access folders created by this integration
Invalid characters not allowed:
< > : " / \ | ? *
Folder Path Examples
reports/monthly # Two levels deep
data/2024/q1/sales # Four levels deep
archives/exports/sheets # Three levels deep
Examples
Append Rows to Existing Google Sheet
{% action "google_sheets" %}
{
"account": "user@example.com",
"operation": "append_rows",
"spreadsheet_id": "1234567890abcdef",
"sheet_name": "Orders",
"rows": [
["Order ID", "Customer", "Total"],
["1001", "John Doe", "99.99"],
["1002", "Jane Smith", "149.99"]
]
}
{% endaction %}
Create New Google Sheet
{% action "google_sheets" %}
{
"account": "user@example.com",
"operation": "create_spreadsheet",
"title": "Monthly Sales Report",
"rows": [
["Month", "Revenue", "Expenses", "Profit"],
["January", "5000", "3000", "2000"],
["February", "5500", "3200", "2300"]
]
}
{% endaction %}
Export Google Sheet
{% action "google_sheets" %}
{
"account": "user@example.com",
"operation": "export_spreadsheet",
"spreadsheet_id": "1234567890abcdef",
"file_type": "pdf"
}
{% endaction %}
Dynamic Data Example
{% assign order_rows = array %}
{% assign header_row = array %}
{% assign header_row["Order", "Customer", "Total"] %}
{% assign order_rows[header_row] %}
{% for order in shop.orders %}
{% assign order_row = array %}
{% assign order_row[order.name, order.customer.name, order.total_price] %}
{% assign order_rows[order_row] %}
{% endfor %}
{% action "google_sheets" %}
{
"account": {{ options.google_account | json }},
"operation": "append_rows",
"spreadsheet_id": {{ options.spreadsheet_id | json }},
"rows": {{ order_rows | json }}
}
{% endaction %}
Create Google Sheet in a Folder
{% action "google_sheets" %}
{
"account": "user@example.com",
"operation": "create_spreadsheet",
"folder_path": "reports/2024/monthly",
"title": "March Sales",
"rows": [
["Date", "Revenue", "Units"],
["2024-03-01", "5000", "50"],
["2024-03-02", "6000", "60"]
]
}
{% endaction %}
Read Data From Google Sheet
mechanic/user/trigger
mechanic/actions/perform
{% if event.topic == "mechanic/user/trigger" %}
{% action "google_sheets" %}
{
"account": {{ options.google_account__required | json }},
"operation": "export_spreadsheet",
"spreadsheet_id": {{ options.spreadsheet_id__required | json }},
"file_type": "csv"
}
{% endaction %}
{% endif %}
{% if event.topic == "mechanic/actions/perform" %}
{% if action.type == "google_sheets" and action.run.ok %}
{% assign sheet_data = action.run.result.data_base64 |
base64_decode | parse_csv: headers: true %}
{% action "echo" sheet_data %}
{% endif %}
{% endif %}
Action Responses
The action returns different responses based on the operation performed:
append_rows Response
{
"spreadsheet_id": string,
"updated_range": string,
"updated_rows": number,
"updated_columns": number,
"spreadsheet_url": string
}
Example:
{
"spreadsheet_id": "1234567890abcdef",
"updated_range": "Sheet1!A1:C3",
"updated_rows": 3,
"updated_columns": 3,
"spreadsheet_url": "https://docs.google.com/spreadsheets/d/1234567890abcdef"
}
create_spreadsheet Response
{
"spreadsheet_id": string,
"spreadsheet_url": string,
"title": string
}
Example:
{
"spreadsheet_id": "1234567890abcdef",
"spreadsheet_url": "https://docs.google.com/spreadsheets/d/1234567890abcdef",
"title": "Monthly Sales Report"
}
create_spreadsheet Response with Folder
{
"spreadsheet_id": string,
"spreadsheet_url": string,
"title": string,
"folder_path": string
}
Example:
{
"spreadsheet_id": "1234567890abcdef",
"spreadsheet_url": "https://docs.google.com/spreadsheets/d/1234567890abcdef",
"title": "March Sales",
"folder_path": "reports/2024/monthly"
}
export_spreadsheet Response
{
"spreadsheet_id": string,
"name": string,
"size": number,
"file_type": string,
"data_base64": string
}
Example:
{
"spreadsheet_id": "1234567890abcdef",
"name": "Monthly Sales Report",
"size": 12345,
"file_type": "pdf",
"data_base64": "base64encodeddata..."
}
Last updated
Was this helpful?