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, use the Row Objects field to add rows during creation. The Spreadsheet option is available if the datasource has access to all spreadsheets. If access is restricted to selected sheets, the Spreadsheet option is disabled.

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​