All Collections
Editor Questions
How-To Questions
Google Sheets. How to Pull Data from Google Sheets into Emails?
Google Sheets. How to Pull Data from Google Sheets into Emails?

This article describes how to automatically extract data from Google Sheets in order to update/edit the content of outgoing emails.

Elmira avatar
Written by Elmira
Updated over a week ago

AMP for email allows senders to include AMP components inside rich, engaging emails, making modern app functionality available within the email. The AMP email format provides a subset of AMPHTML components for use in email messages, allowing recipients of AMP emails to interact dynamically with content directly in the message.

Emails written with AMP can contain interactive elements such as: an image carousel or accordions, real-time content, the type of content that is always up-to-date/updated in the email when the email is opened, or gamification.

Let's take a closer look at the real-time content function:)

What is real-time content?

Real-time content helps you update your email's content even after it has been sent to users. This means that your customers will always be able to see only fresh, relevant, and real information. By creating such emails in Stripo, you will no longer need to go to your servers and deal with CORS headers for that.

Therefore we can offer you to use pulling data from Google Sheets.


How to Pull Data from Google Sheets into Emails via Stripo?

Step 1: Setting up Google Sheets as a data source:

  • In the Stripo account, find the "Data" tab;

  • Click on the "Connect a Data Source" button;

  • Give the source a name (required) and a short description (optional);

  • All other fields are filled in automatically;

  • Besides, in the "Additional Options" tab you can enable the "Randomizer" option;

The Randomizer feature for AMP-lists provides the ability to retrieve content in random order - product cards inside the array are returned in haphazardly.

  • In the "Data Settings""Google Sheets" menu, please click on the "Sign in with Google" button;

  • After that, the system will ask you to log in to Gmail and select an account;

  • In the pop-up window, you should give Stripo access to the Google Sheets by clicking on the "Allow" button;

Important note: Stripo has passed Google's security check. Therefore, you can completely entrust your tables to us since this is the only thing our data source will have access to (only to get data).

  • Please choose the desired table by clicking on it and confirm by clicking on "Select";

  • For your convenience, we show the name of the Spreadsheet and the number of its sheets. By default, we connect to the first one, but you can switch to any sheet from the selected Google Spreadsheet;

  • In your Data Source, you can check if all the information is extracted correctly, like names of columns, names of rows;

  • You can also disable unnecessary lines (even after the email has been sent).

You just need to scroll to the right and uncheck some elements;

  • Please click the "Connect a Sheet" in the bottom right corner;

  • Furthermore, you can add additional options such as:

    -The First Line Contains the Headers. If your document does not contain a heading, we will add headings by default, for example, "Column 1", etc.

    -Include Sheet Name in the Generated JSON File. The data will be enclosed in an additional array named after the selected sheet.

  • When you click on it, the Data Source will be updated;

  • After that, copy the URL as you will need it later;


Step 2. Preparing your Google Spreadsheet:

  • Create a new Google spreadsheet on your drive;

  • Name the required columnswe recommend giving clear but simple names that contain only one word, as you will need to insert them into emails;

Important to note: They must be written in Latin, as we will add them to the email code.

  • Fill in your spreadsheet with the necessary information.

The "Position" column is necessary if you put two containers in 1 row.



Step 3. Preparing your email:

First of all, you need to prepare your email:

  • Create product cards from buttons to the location of pictures and font size for descriptions;

  • Activate the "HTML" options for this structure;

  • Then open its code and copy all the below, starting from the <table cellpadding="0" cellspacing="0"...> line and up to the next <table cellpadding="0" cellspacing="0"> line doesn't inclusive it.


Important note:

I copied only half of the code — the code of the entire cell for my product card — because I built a row with two containers. But if you put just one container per row, you will have to copy all the code in the code editor (for this particular email element).

You will also need to paste it once, not twice, no matter how many product cards per row and rows you are about to have — this code sample is repetitive. So, in the end, in your emails, you will have as many product cards as many lines you’ve filled out in your Google Spreadsheet.

  • After that, add a new one-column structure into your HTML template;

  • Drag the "HTML" block;

  • Open its HTML code and paste the part of the code you just copied.

  • Now connect your emails to the Google Sheet you’ve just created.


Step 4. Connecting Google Spreadsheets to emails:

  • At the beginning of the code (when you opened the code structure), please add the following code;

<amp-list layout="fixed-height" height="360" width="auto" src="https://stripo.email/emailformdata/v1/list/ecxs/stripo-welcome-emails">
<template type="amp-mustache">

Result:


  • Then replace the link in the "src=" part with the Source URL address from the generated Data source.

This is the Source URL (you can find your URL in the Data tab).

I have replaced it with my Source URL.

  • Paste this part of the code below at the very end of this Structure code;

</template>
</amp-list>

Result:

  • Part of the code (responsible for the pictures) which is highlighted in the screenshot below

It should be replaced with the code below;

<a href="{{URL}}" target="_blank"><amp-img width="269" height="180" alt="{{Productname}}" src="{{imgURL}}" layout ="responsive">
</amp-img>
</a>

(You can set the width and height to suit your email design)

Result:

Where:

  • {{Productname}} is responsible for the name of the product - the one you entered in your Google spreadsheet;

  • {{URL}} is a link to a product on your site;

  • {{imgUrl}} contains a link to the snippet that you also added to the Google Sheet.

If you have given other column names in your tables, put them in curly braces accordingly.

Now we need to link other columns from the table to your email:

  • When creating product cards, for convenience, I gave the elements of the names of the cards that are close to the names in the columns of the table.

  • So when I opened the code for my product cards, it only took me two minutes to link my email to Google.

Two small clarifications:

  • I had to wrap all these names in curly braces;

  • All elements that were also wrapped in quotation marks must remain within those quotation marks.

When you're done, don't forget to enable the HTML option for this entire row/structure

After you have done all these steps, you can go to the preview to check the result:)


Creating a fallback:

What is a fallback email? We know that not all email clients will be able to render the amp component in the email, so we can take care of those clients and make a fallback version of displaying a content part, including it in the HTML version.

It is created very easily and does not require any special knowledge. The main rule is to make a special switch to the HTML version only.

Let's consider our example of the fallback: we put an article's title in the email and attach a link to a button that leads to the site where the recipients can see absolutely all available articles. This way, our recipients will still be able to view our articles even if they don't support amp.

  • Firstly, add a 1-container structure into the template;

  • Design it the way you like; add a "Text" block with the article's title;

  • Drop a "Button" block and paste a link to your website where the articles are located;

  • Click on the Structure with all this content;

  • And include this structure in the "HTML" email version only (as shown above).


How to test AMP emails?

  • You can test it in your Gmail box when you send the test from Stripo. Please click on the "Test" button above the template;

  • In a pop-up window, enter your email address and click the "Send Test" button;

  • You should go into your mailbox settings in Gmail and turn on the option to receive dynamic content;

  • Please delete all the test emails (for that current email, if you tested it before) that you sent earlier and send the test again.

After all these steps, you will be able to test displaying your AMP emails.


After you've tested the emails and made sure it's ready to send, let's look at the option to export AMP emails:

How to export AMP emails?

Important to note: not all possible email services and clients support AMP functionality.

Here is an official Google page https://amp.dev/support/faq/email-support/.

Here you can check which email support services send AMP emails and which email providers have AMP support content.

Currently, this is the list of ESPs that do support sending of AMP emails:

In order to export a finished AMP email, please click on the Export button in the Stripo editor, choose "Support for AMP", and you will be able to export the AMP email directly to your ESP.

If you see that your ESP is not listed but it supports AMP technology, then you should export the email as AMP HTML — this export method saves the scripts that are appropriate for the MIME-type. Import this file into the ESP that supports Google AMP for emails.

Proceed to "Files" and then to AMPHTML.

Please be informed that only 4 email clients (GMAIL, YAHOO, MAIL.RU, FAIREMAIL) support displaying AMP content as recipients.


Getting Whitelisted with Google and Yahoo

If you want to make your own sending, your mail should be white-listed in the Google/Yahoo service, so now I will tell you how to do it in just a few steps.

To get whitelisted with Gmail and Yahoo, you need to:

This form is designed for both mail services that support AMP.

After submitting the registration form, you will see the following notification:

You will not receive any other notification from Google or Yahoo until you are whitelisted.

To find the whole instruction, we wrote an article and covered it in great detail How to Whitelist Google for Sending AMP Emails: Sharing Our Experience.

Please read it if you are determined to go through it.


Thank you for taking the time to read our articles. We hope you will find this information helpful.

If you have any additional questions, please email us at support@stripo.email.

We would be glad to talk with you.


Did this answer your question?