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,

How To Send Image Generation API Request With Google Sheet

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.

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.

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.

Here's the modified script.

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.