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.

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.

  1. Send image generation request

  2. 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!

Ready to dive in?Start your free trial today.