Learn how to call any API with Google Sheet AppScript. In this tutorial, we will call Stencil API to generate images based on the data we have in our sheet. We will also learn how to create a drawing that acts as button to trigger our script.
In our previous Automation Series, we used Airtable in various ways to automate image generation workflow. Although Stencil doesn't have native integration with Google Sheet, this is not an issue at all. You have seen how you can call Stencil's API to generate image with Airtable. You can do the same with Google Sheet.
If you're interested in learning more about our Airtable Automation Series, you can read them here,
Both services provide a similar use case but they are not exactly offering the same features because they both work differently.
Google Sheet can be seen as more traditional spreadsheet like Microsoft Excel. It's geared more towards data processing, number crunching, statistics, etc.
Airtable is similar but with richer elements where fields can be formatted into a richer format like image, button, attachments and more.
Which one to choose? It is totally up to you depending on your use case and needs. Either way you can still use Stencil to handle image generation.
So let's start!
Setting up Google Sheet to work with Stencil is easy. Once you have your data in your sheet, the only thing left to do is to create additional column as a placeholder for the generated image link. In our table setup below, we created a column called Output.
Our table setup in Google Sheet
Checkout our free product ads generator here https://www.usestencil.com/demo/ecommerce. We have more demos available in our site.
Template that we will use
In order to call Stencil API, we are going to write App Script. If you're a developer familiar with JavaScript, you will feel at home. Even if you don't, you can simply copy our code below and make adjustment that fits your table.
To create your first script, click on Extensions > App Script. App Script editor will pop out. For this tutorial, we are going to name our function generateImage.
See the GIF below for this process.
Create a script for your sheet
What this script does is it calls Stencil API to generate image synchronously and once the image is generated, it will populate the output column with the generated image link.
There are few things that you need to replace in order to get this script running.
Change the API_KEY
Change the template id
Change the modifications to match your template
We have commented the code below with some explanation to help you understand it.
JAVASCRIPT const API_KEY = ""
function generateImage() {
let rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
rows.forEach((row, index) => {
// skip row 0 because it only contains the header
if (index != 0) {
// Ouput column is at column 5
let range = SpreadsheetApp.getActiveSheet().getRange(index + 1, 5)
// Our data is fro column 0 to column 3
let title = row[0]
let description = row[1]
let image = row[2]
let price = row[3]
let data = {
'template': '0b142d3f-92e0-48ba-9ecf-53b05bbec031',
'modifications': [
{
"name": "image",
"src": image
},
{
"name": "description",
"text": description
},
{
"name": "price",
"text": price
},
{
"name": "product",
"text": title
}
]
}
let options = {
'method': 'post',
'contentType': 'application/json',
'headers': {
'Authorization': `Bearer ${API_KEY}`
},
'payload': JSON.stringify(data)
}
let result = UrlFetchApp.fetch("https://api.usestencil.com/v1/images/sync",options)
let json = JSON.parse(result.getContentText())
range.setValue(json.image_url)
// sleep so we don't get throttled
Utilities.sleep(250)
}
})
}
Once that is done we are almost ready to generate our images. We need to add a trigger point to call our function.
Unlike Airtable which has a dedicated Button field, Google Sheet does not have that. Instead, with Google Sheet you can insert a drawing and assign a script to the drawing. So we are going to draw a button and assign our previous script to the button.
When the button is clicked, it will run our function.
All you have to do now is just click on the button whenever you want to generate your images.
Link to the image will be populated back to the Output column
Now regardless of where you data lies, you can always automate your content creation process.
Since we are using Stencil synchronous image generation API, you need to have the window open and wait until everything is generated.
In fact, this is not necessary if we are using Stencil asynchronous image generation API. In our next series of Google Sheet automation, you will see how we can do that. So stay tune!