Apr 23, 2022
How To Create Auto Generating Image Web App With Google Sheet
Learn how to build a simple auto generating image web app that calls Stencil API to generate marketing ads from data in Google Sheet.
Shulhi Sapli
Founder
In our previous tutorial, we learned how to turn your sheet data into images by creating a script that calls Stencil API.
However, there was one limitation that we discussed at the end of the tutorial in which you would have to wait for all the images to be generated. Closing the page will stop the image generation process. It would be nice if we can work around this limitation. This tutorial will exactly do that.
You can find the previous tutorial here,
Setting Up Google Sheet
Our setup is exactly the same as our last tutorial. We are going to create an additional column called output that will hold the generated image links.
Our sample table setup
Deploying Google Sheet As Web App
Similar to our previous tutorial, we are going to write a small script that calls Stencil API to generate image asynchronously.
Our script will look very similar to what we had previously but with a little twist. This time around, we are going to deploy our script as a web app. How does it help?
Deploying your sheet as web app allows you to trigger an action (i.e. script handler) from a given URL.
We will pass this URL to Stencil. Stencil allows you to specify a webhook that can be triggered when an image is generated.
When the webhook is called, the script handler will receive the information about the generated image and we will update the sheet accordingly.
Deploying as web app
Writing App Script
There are two parts to this script.
Send image generation request
Handle the callback when the webhook is triggered
The process is similar like in our previous tutorial.
How to create an App Script
Here's the modified script.
JAVASCRIPT const API_KEY = ""
// This is triggered when the webhook is called. Stencil will pass along the response as the payload
function doPost(e) {
const body = JSON.parse(e.postData.contents)
// Get the row from the metadata field that we set when we sent the request
const row = body.metadata.row
// Set column 5 (Output) with the image_url value
let range = SpreadsheetApp.getActiveSheet().getRange(row + 1, 5)
range.setValue(body.image_url)
}
function generateImage() {
let rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
rows.forEach((row, index) => {
if (index != 0) {
let range = SpreadsheetApp.getActiveSheet().getRange(index + 1, 5) // output column
let title = row[0]
let description = row[1]
let image = row[2]
let price = row[3]
// NOTE
// 1. Set the webhook_url with the URL given by Google Sheet Web App
// 2. Set metadata value to keep our row number so we know which row to update when the webhook is triggered
let data = {
'template': 'YOUR_TEMPLATE_ID',
'modifications': [
{
"name": "image",
"src": image
},
{
"name": "description",
"text": description
},
{
"name": "price",
"text": price
},
{
"name": "product",
"text": title
}
],
'metadata': {
'row': index
},
'webhook_url': 'https://script.google.com/macros/s/xxxxx/exec'
}
let options = {
'method': 'post',
'contentType': 'application/json',
'headers': {
'Authorization': `Bearer ${API_KEY}`
},
'payload': JSON.stringify(data)
}
// Call the async image generation
let result = UrlFetchApp.fetch("https://api.usestencil.com/v1/images",options)
let json = JSON.parse(result.getContentText())
range.setValue(json.image_url)
// sleep so we don't get throttled
Utilities.sleep(250)
}
})
}
Generating Images Asynchronously
Before we can run the script, we can create a button and assign a script to the button. When clicked it will call the image generation API asynchronously and you don't have to wait for all images to be generated.
Creating a button from a drawing
In fact, you can even close the tab and the output column is still going to be populated with the link to the generated images.
Generating your images
Now you can start automating your marketing assets with Google Sheet!