Create an Email Query Using Data From Google Sheets in Appsmith

Issue

I want to incorporate data from multiple Google Sheets into emails on Appsmith. I added columns to the sheets and want to include the new information in the email. I need assistance on how to retrieve the specific information and display it clearly in the email. Additionally, I want to know how to access data with matching IDs in two different sheets and include that information in the email as well.

Resolution

This guide explains how to retrieve data from one or more Google Sheets in Appsmith and include it in an email query. It also covers how to match records across different sheets using a common ID and format the data clearly in the email body.

Prerequisites

A connected Google Sheets datasource in Appsmith
Access to the relevant Google Sheets
An Email / SMTP datasource configured in Appsmith

You can refer to the official documentation for setup details: Google Sheets datasource documentation

Step 1: Fetch Data from Google Sheets

Create a new Query using the Google Sheets datasource.
Select the appropriate Spreadsheet and Sheet.
Use the Fetch Many operation to retrieve rows.

The query result will be available as:

{{ FetchSheet1.data }}

If you added new columns to the sheet, they will automatically appear in the query response as long as:

The header row is updated
The query is re-run

Step 2: Access Specific Columns for the Email

You can reference individual fields directly in the email body using mustache syntax.

Example

Name: {{ FetchSheet1.data[0].Name }}
Email: {{ FetchSheet1.data[0].Email }}
Status: {{ FetchSheet1.data[0].Status }}

To format multiple rows, use .map() inside a JS expression:

{{ 
  FetchSheet1.data.map(row => 
    `Name: ${row.Name}, Status: ${row.Status}`
  ).join("\n")
}}

Step 3: Match Data Between Two Google Sheets Using an ID

If you have two sheets with a shared identifier (for example, userId):

Sheet A: User details
Sheet B: Order or status details

Create two separate queries:

FetchUsers
FetchOrders

Then use a JSObject to combine the data.

Example JSObject function

export default {
  mergeData() {
    return FetchUsers.data.map(user => {
      const match = FetchOrders.data.find(
        order => order.userId === user.userId
      );
      return {
        ...user,
        orderStatus: match ? match.status : "N/A"
      };
    });
  }
}

The merged result can be accessed as:

{{ JSObject1.mergeData() }}

Step 4: Use the Merged Data in an Email Query

In your Email query body (single record):

Hello {{ item.Name }},

Your current order status is: {{ item.orderStatus }}

If sending multiple records in one email:

{{ 
  JSObject1.mergeData().map(item => 
    `Name: ${item.Name}\nOrder Status: ${item.orderStatus}\n`
  ).join("\n---\n")
}}

Tips for Clear Email Output

Use line breaks (\n) for readability
Label each field clearly
Test output by binding the same expression to a Text widget before sending the email

Note: Before sending emails, validate your merged output by binding it to a Text widget. This helps confirm your IDs match and your template renders correctly.

Updated