Skip to main content

Fetch and Filter Data from Google Sheets

This page shows you how to fetch and filter data from Google Sheets.

Prerequisites​

  • An app connected to a Google Sheets datasource.
  • A Table widget.
  • A query configured to fetch all the rows from a specific Google Sheet and bind to the Table widget.

Using where clause​

To fetch data based on a condition using the where clause, follow these steps:

  1. In the query, select Fetch Many in Operations.

  2. In Filter Format, select Where Clause.

  3. In Filter By, enter the filter conditions that you want to apply. You can add multiple conditions or group conditions and combine them using AND, OR.

    Filter Google Sheet data using where
    Filter Google Sheet data using where clause

    For example, to bind the value of a Text widget named gender to the where clause, use the following code:

    {{ gender.text; }}

  4. In Sort By enter the column name you want your data sorted by.

info

If the Smart JSON substitution setting is enabled, Appsmith adds or removes quotation marks from the mustache binding {{}} as necessary to correctly cast them into JSON. You must manually format the JSON data if this setting is off. For a video guide on using this feature, see How to Use Smart JSON Substitution.

Using a cell range​

To filter data by a range to fetch data from designated cells in your sheet, follow these steps:

  1. In Filter Format, select Cell range.

  2. Specify the range of the cells to fetch data in the following format:

    A2: Z;

    Selecting cells in this mode uses Google Sheets' row number and column letter syntax.

    For example:

    A1 - B14;

    Your fetched data still includes the column labels even if the column header row does not appear in your selection.

    For more information, see Cell Range.

  3. To dynamically bind the cell range using mustache syntax, use the following format where Cell_range_row and Cell_range_col are two Text widgets:

    {{Cell_range_row.text}}:{{Cell_range_col.text}}

Server-side Pagination​

You can configure the page size in the response when the Filter Format is set to Where Clause. To set the number of records fetched in the response dynamically, follow these steps:

  1. In Pagination Limit set the value according to the page size using the following code where user_details_table is the name of the Table widget:

    {{ user_details_table.pageSize; }}

    When you set the Pagination Limit dynamically, Appsmith automatically updates the number of records to be fetched in the response based on your page or table size.

  2. Set the Pagination Offset based on the current page size using the following code:

    {{ user_details_table.pageOffset; }}

  3. To set up the Server-side pagination for the Table widget, see Setup Server-Side Pagination on Table.

See also​