Automate image generation
Save time on creating similar images with different texts and branding!
Create account
Share article

Image Generation with Google Sheets

Published June 04, 20226 min read

Image Generation with Google Sheets

Google Sheets is a web-based online spreadsheet editor with multiple options and customization features that help you automate your workflow. Using simple scripts, we can add external automations to your Google Sheet spreadsheet, which is a great option to improve your data management.

Today, I will show you how to automate image generation using Google Sheet spreadsheet and a custom image generation script.

Create image template

Before we start with Google Sheets script configuration, prepare your image template in RenderForm. Remember that one element in your image template, for example text, is one component that can be editable via Google Sheets automation.

Create image template in RenderForm

Step 1: Add Google Apps script

In Google Sheets, open 'Extensions' menu in the top and click 'Apps Script' option.

Open Google Sheets extension

Google Sheets will redirect you to a script editor in a new window.

Script for image generation.

Copy and paste the script below into the script editor.

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    { name: 'Render images...', functionName: 'generateImages' }
  ];
  spreadsheet.addMenu('RenderForm', menuItems);
}

String.prototype.addQuery = function(obj) {
  return this + Object.keys(obj).reduce(function(p, e, i) {
    return p + (i == 0 ? "?" : "&") +
      (Array.isArray(obj[e]) ? obj[e].reduce(function(str, f, j) {
        return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
      },"") : e + "=" + encodeURIComponent(obj[e]));
  },"");
}


function generateImages() {
  var apiKey = Browser.inputBox('RenderForm API Key', 'Please enter the RenderForm API Key', Browser.Buttons.OK);
  try {
    checkApiKey(apiKey);
  } catch (e) {
    Browser.msgBox("Incorrect API Key", "Please check again if your API Key is valid.", Browser.Buttons.OK);
    return;
  }


  try {
    var sheet = SpreadsheetApp.getActiveSheet();
    var sheetData = sheet.getDataRange().getValues();
    var renderFormElements = [];
    var renderFormUrlIndex = sheetData[0].indexOf('rf.image_url');

    if (renderFormUrlIndex === -1) {
      Browser.msgBox("Missing output column", "Please add column named 'rf.image_url' in order to get image results.", Browser.Buttons.OK);
      return;
    }


    var template = Browser.inputBox('RenderForm Template', 'Please enter the RenderForm Template Identifier', Browser.Buttons.OK);

    var firstRow = sheetData[0];
    for (var columnIndex = 0; columnIndex < firstRow.length; columnIndex++) {
      var columnName = sheetData[0][columnIndex];
      var isRenderFormColumn = columnName.startsWith("rf.");
      var isRenderFormImageUrlColumn = columnName === "rf.image_url";
      if (isRenderFormColumn && !isRenderFormImageUrlColumn) {
        var renderFormElement = {
          columnIndex: columnIndex,
          name: columnName
        };
        renderFormElements.push(renderFormElement);
      }
    }
  } catch (e) {
    Browser.msgBox("Invalid spreadsheet", "Please check again your spreadsheet column.", Browser.Buttons.OK);
    return;
  }

  Browser.msgBox("Rendering is starting", "Images render will take a while, please do not edit spreadsheet until the script finish.", Browser.Buttons.OK);
  for (var rowIndex = 1; rowIndex < sheetData.length; rowIndex++) {
    try {
      var row = sheetData[rowIndex];
      var requestParams = {
        apiKey,
        output: "json",
      };

      for (var element of renderFormElements) {
        var columnIndex = element.columnIndex;
        var name = element.name;
        var value = row[columnIndex];
        var property = name.replace("rf.", "");
        requestParams[property] = value;
      }

      var renderFormUrlCell = sheet.getRange(rowIndex + 1, renderFormUrlIndex + 1);
      renderFormUrlCell.setValue("Loading...");
      var imageUrl = renderImage(template, requestParams)
      console.log("Rendered:", imageUrl);
      renderFormUrlCell.setValue(imageUrl);
    } catch (e) {
      if (renderFormUrlCell) {
        renderFormUrlCell.setValue("Error: " + e);
      }
    }
  }
  Browser.msgBox("Completed", "RenderForm finished rendering your images.", Browser.Buttons.OK);
}



function checkApiKey(apiKey) {

  var request = {
    'method': 'get',
    'contentType': 'application/json; charset=utf-8',
    'headers': {
      'x-api-key': apiKey
    }
  };

  var response = UrlFetchApp.fetch('https://get.renderform.io/api/v1/organization', request);
  var json = response.getContentText();
  var responseBody = JSON.parse(json);
  return responseBody.name;
}

function renderImage(templateId, requestParams) {
  var baseUrl = "https://get.renderform.io/img/";
  var requestUrl = (baseUrl + templateId).addQuery(requestParams);
  var response = UrlFetchApp.fetch(requestUrl);
  var json = response.getContentText();
  var responseBody = JSON.parse(json);
  return responseBody.href;

}

Paste image generation script to Google Apps

After pasting the code above you click 'Run' and follow the Google instructions which may appear.

Step 2: Configure your data

Let's go back to the tab with your data in Google Sheets. Create a new column in your spreadsheet called rf.image_url. RenderForm will generate image URLs basing on your data, and put the links there.

The script will retrieve data from columns that start with rf. prefix, and it will replate them in selected components while generating images.

Column names must also contain image component and its property, e.g.:

Template Editor Component ID Component Type Google Sheets column name
my_image Image rf.my_image.src
my_text Text rf.my_text.text
my_rating Rating rf.my_rating.value
my_qr_code QR Code rf.my_qr_code.value

Google sheets for Image Generation

If you use HTML Templates, then you can skip the component part and use only properties, e.g.: rf.my_property.

Step 3: Run your script

Once your data is ready, you can start the script! Click 'RenderForm' and click 'Render images...'.

Start image rendegin in Google Sheets

RenderForm script will ask you for your API Key and Template Identifier. You will find your RenderForm API Key in your account section. Template ID is the identifier on the right panel of your template editor.

Start image generation in Google Sheets

Thereafter, the script will start generating images and put the image links to the rf.image_url column. This process may take a while, please do not edit the spreadsheet until the script finish.

Generated images in Google Sheets

Relevant posts

Image Generation with Airtable Extension

Image Generation with Airtable Extension

Jakub PomykałabyJakubOctober 31, 2022

Automate image generation using Airtable. Create images using your templates, or take website screenshot with Airtable.

Read more
Image Generation with Zapier

Image Generation with Zapier

Jakub PomykałabyJakubAugust 17, 2022

Automate image generation using Zapier and RenderForm. Create mutiple images with your templates, or take website screenshot using Zapier.

Read more
Image Generation with Airtable Script

Image Generation with Airtable Script

Jakub PomykałabyJakubJune 17, 2022

Automate image generation using Airtable. Create images using your templates, or take website screenshot with Airtable.

Read more
What is Image Generation?

What is Image Generation?

Jakub PomykałabyJakubJune 04, 2022

Learn what is image automation. Grow your business with no-code automation tools. Generate images using REST API, Zapier, Make, Google Sheets, and more!

Read more
What is RenderForm?

What is RenderForm?

Jakub PomykałabyJakubJune 04, 2022

Learn more about RenderForm, its features and how you can scale your designs with external tools like Zapier and Make.

Read more
Dynamic Social Images for Wordpress

Dynamic Social Images for Wordpress

Jakub PomykałabyJakubJune 04, 2022

Automatically generate social media image previews for Wordpress blog posts with the RenderForm.

Read more

Design. Automate. Scale.

Free plan • No credit card required • Get started in minutes