Skip to main content

Google Sheets

This page provides information for connecting Appsmith to Google Sheets and for reading and writing data in your applications.

Connection parameters

Configuring a new Google Sheets datasource.
Configuring a new Google Sheets datasource.

Permissions | Scope:

Defines the privileges your app has when querying spreadsheets. Use this to allow the minimum necessary privileges for your app's functions.

Options:
  • Read / Write / Delete | Selected Google Sheets: Your app has read, write, and delete access only for the sheets that you specify while authorizing the datasource.
  • Read / Write / Delete | All Google Sheets: Your app has read, write, and delete access for all sheets on your Google account.
  • Read / Write | All Google Sheets: Your app has read and write (but not delete) access for all sheets on your Google account.
  • Read | All Google Sheets: Your app has read-only access to all sheets on your Google account.

Clicking Save and Authorize takes you to a Google login where you can authorize your account and select your sheets.

Query Google Sheets

The following section is a reference guide that provides a complete description of all the parameters to connect to Google Sheets.

Configuring a query from the query screen.
Configuring a query from the query screen.

Fetch Details

This command fetches metadata for a given Spreadsheet entity. The following section lists all the fields available for the Fetch Details command.

Entity

Sets which entity type to query

Options:
  • Spreadsheet: Returns metadata for a spreadsheet document.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Insert One

This command inserts a given entity type: Sheet Row(s) or Spreadsheet. The following section lists all the fields available for the Insert One command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Inserts a single record as a row in the spreadsheet.
  • Spreadsheet: Creates a new spreadsheet document. Optionally, you can use the Row Objects field to provide rows that should be created along with the document.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Table Heading Row Index

The index of the row in your spreadsheet that contains the headings or labels for your table columns. The first row of the spreadsheet is index 1.

Row Object

Available when the Entity is Sheet Row(s). This expects a JSON-formatted object whose key/value pairs represent the columns and values from your table record.
Example:
{
  "name": {{ UserForm.name }},
  "email": {{ UserForm.email }},
  "status": "pending"
}

Row Objects

Available when the Entity is Spreadsheet. This expects an array of JSON-formatted objects whose key/value pairs represent columns and values to add to your new spreadsheet when it is created.
Example:
[{
    "name": "Kim",
    "email": "hkim@example.com",
    "status": "accepted"
},
{
  "name": "Amal",
  "email": "samal@example.com",
  "status": "accepted"
}]

Update One

This command updates a Sheet Row(s) entity. The following section lists all the fields available for the Update One command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Updates a single existing row in the spreadsheet.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Table Heading Row Index

The index of the row in your spreadsheet that contains the headings or labels for your table columns. The first row of the spreadsheet is index 1.

Update Row Object

A JSON-formatted object whose key/value pairs represent the columns and values from your table record. You must include a rowIndex key to specify which record to update. If you fetched the record from another Google Sheets query, this index value should be available on its rowIndex property.

Example:

//For JSON Form: {{JSONForm1.formData.id}} 
//For Table inline editing: {{ Table1.updatedRow.id }}

{{
{
rowIndex: Table1.selectedRow.rowIndex, // includes rowIndex key
"id": Form1.data.TextID,
"name": Form1.data.InputName,
"country": Form1.data.SelectCountry

}
}}

See Update single row guide.

Delete One

This command deletes a given entity: Sheet Row(s), Spreadsheet, or Sheet. The following section lists all the fields available for the Delete One command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Deletes a single row of a spreadsheet.
  • Spreadsheet: Deletes a new spreadsheet document.
  • Sheet: Deletes a page from a spreadsheet document.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Row Index

The index of the record to delete from the spreadsheet. If you fetched the record from another Google Sheets query, this index value should be available on its rowIndex property.

Fetch Many

This command fetches a given entity type: Sheet Row(s) or Spreadsheet. The following section lists all the fields available for the Fetch Many command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Fetches a subset of horizontal records from a page of a spreadsheet document.
  • Spreadsheet: Fetches a list of existing spreadsheet documents.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Table Heading Row Index

The index of the row in your spreadsheet that contains the headings or labels for your table columns. The first row of the spreadsheet is index 1.

Filter Format

Sets the method of selecting records from your spreadsheet.


Options:
  • Where Clause: Fetches records based on logic and conditions. This also allows you to sort and paginate your results.
  • Cell Range: Fetches a block of spreadsheet cells defined by spreadsheet-style notation, such as A2:B7.

The following settings are available when Filter Format is set to Where Clause:

Filter By

This is used to build expressions that return records when a column value meets some criteria. You can evaluate records using in, not in, contains, and logic operators.
Buttons:
  • Add Condition: Adds another simple single-line expression.
  • Cell Range: Adds a nested expression with multiple levels of And/Or statements.

Sort By

Sorts the resulting records according to the specified column.
Buttons:
  • Add Parameter: Adds another column for sorting on multiple levels.

Pagination Limit

Limits the number of records you can receive in a single response. Use with Pagination Offset to implement pagination for large datasets.

Pagination Offset

Allows skipping a given number of records before returning results. Use with Pagination Limit to implement pagination for large datasets.

The following setting is available when Filter Format is set to Cell Range:

Cell Range

This mode uses Google Sheets' row number and column letter syntax (such as A1-B14) to select cells. Even when the column header row isn't part of your selection, your fetched data still includes the column labels for your selected cells. This mode doesn't allow conditions, sorting, or pagination.


See how-to guide on Fetch and Filter Data from Google Sheets.

Insert Many

This command inserts multiple Sheet Row(s) entities. The following section lists all the fields available for the Insert Many command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Inserts several records as a rows in the spreadsheet.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Table Heading Row Index

The index of the row in your spreadsheet that contains the headings or labels for your table columns. The first row of the spreadsheet is index 1.

Row Objects

Expects an array of JSON-formatted objects whose key/value pairs represent columns and values to add to the spreadsheet.
Example:
[{
    "name": "Kim",
    "email": "hkim@example.com",
    "status": "accepted"
},
{
  "name": "Amal",
  "email": "samal@example.com",
  "status": "accepted"
}]

See how-to guide on Insert and Update Data in Google Sheets.

Update Many

This command updates multiple Sheet Row(s) entities. The following section lists all the fields available for the Update Many command.

Entity

Sets which entity type to query.

Options:
  • Sheet Row(s): Updates multiple existing rows in the spreadsheet.

Spreadsheet

The name of the spreadsheet document you'd like to query.

Sheet Name

The name of the page you'd like to query from your spreadsheet.

Table Heading Row Index

The index of the row in your spreadsheet that contains the headings or labels for your table columns. The first row of the spreadsheet is index 1.

Update Row Object(s)

An array of JSON-formatted objects whose key/value pairs represent the columns and values from your table record. You must include a rowIndex key in each row object to specify which record to update in the spreadsheet. Note that the rowIndex property of your row objects in Appsmith refers to its index in the array of table records, not the record's row number in the Google spreadsheet.

Example:

{{
Table1.updatedRows.map(row => { // includes rowIndex key in each object
return row.allFields
})
}}

See Update multiple rows guide.

Troubleshooting

If you are experiencing difficulties, you can refer to the Datasource troubleshooting guide or contact the support team using the chat widget at the bottom right of this page.

See also