How to process images from Google Sheets

A common use case for the Photoroom API is to process a large amount of images, whose URLs are stored in a spreadsheet.

In this tutorial, we'll see how it's possible to process these images by integrating either the Image Editing API or the Remove Background API with Google Sheets.

Let's take the example of this spreadsheet:

To process these images we'll need two things:

1. a place to store the result images

2. a way to run code in the spreadsheet

Storing the images is easy: we'll just use a Google Drive folder.

And to run code inside the spreadsheet, we're going to use something called a Google Apps Script.

Step 1: Creating a Google Apps Script

If you've never used Google Apps Script before, it's a convenient way to run code that can easily interact with Google services, including Google Sheets and Google Drive.

To create an Apps Script, open the Extensions menu and click on Apps Script:

This will open a new tab that contains a development environment:

The first thing we'll do is to allow the script to interact with both Google Sheets and Google Drive.

To do that, click on the “+” button next to Services:

Then proceed to add both the Drive API and Google Sheets API:

You should now see both Drive and Sheets as services available in your development environment:

Now that our environment is set up, it's time to write some code!

You can remove the sample code function myFunction() and replace it with this code:

Code to integrate with the Image Editing API

function processSpreadsheet() {
  // Config variables
  const apiKey = "REPLACE_WITH_YOUR_API_KEY"; // Replace with your API Key
  const folder = DriveApp.getFolderById("REPLACE_WITH_GOOGLE_DRIVE_FOLDER_ID"); // Replace with your Google Drive folder ID
  const edits = "background.color=FFFFFFFF&outputSize=1000x1000&padding=0.1"; // Replace with the edits you want to apply

  const IMAGE_URL_COL = 0;
  const STATUS_COL = 1;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var requests = [];
  var detailsMap = {};

  data.forEach(function (row, index) {
    if (index == 0) return; // Skip header row

    if (row[STATUS_COL] === 'Processed') return; // Skip if row already marked as processed

    var imageUrl = row[IMAGE_URL_COL];
    var apiEndpoint = "https://image-api.photoroom.com/v2/edit?" + edits + "&apiKey=" + apiKey + "&imageUrl=" + encodeURIComponent(imageUrl);

    requests.push({url: apiEndpoint, muteHttpExceptions: true});
    detailsMap[apiEndpoint] = { rowIndex: index }; // Map API endpoint to row index
  });

  // Process in batches
  var batchSize = 8; // Number of concurrent calls
  for (var i = 0; i < requests.length; i += batchSize) {
    var batchEnd = i + batchSize > requests.length ? requests.length : i + batchSize;
    var batch = requests.slice(i, batchEnd);
    var responses = UrlFetchApp.fetchAll(batch);

    responses.forEach(function (response, index) {
      var url = batch[index].url;
      var details = detailsMap[url];
      if (response.getResponseCode() == 200) {
        var blob = response.getBlob();
        blob.setName((details.rowIndex + 1) + ".png");
        folder.createFile(blob);
        sheet.getRange(details.rowIndex + 1, STATUS_COL + 1).setValue('Processed'); // Update the status in the second column
      } else {
        sheet.getRange(details.rowIndex + 1, STATUS_COL + 1).setValue('Error'); // Update the status in the second column
        Logger.log("Error processing row " + (details.rowIndex + 1) + ": " + response.getContentText());
      }
      SpreadsheetApp.flush();
    });
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Create a custom menu in the Google Sheets UI.
  ui.createMenu('Photoroom Actions')
      .addItem('Process through Photoroom API', 'processSpreadsheet')
      .addToUi();
}

There's quite a bit of code. Fortunately, you will only need to configure a few parts:

1. update const apiKey with your Photoroom apiKey

If you don't have an apiKey, here are the steps to create yours.

1. update const folder with the id of the Google Drive folder where you want to save the result images

To get the id of a Google Drive folder, you just need to look at the folder's URL:

https://drive.google.com/drive/u/0/folders/THIS_IS_THE_FOLDER_ID

1. update const edits with the edits you want to apply to your images. In this example, here are the edits that will be applied:

   • white background

   • output size of 1000x1000px

   • padding of 10%

The full list of supported parameters is available in the API reference.

You can visually define the edits you want to apply using the API playground.

That's all the changes you need to make, the rest of the code can be used as-is.

By default, the result image will be named using their respective line number in the spreadsheet. If needed you can update this behavior by changing this line:

blob.setName((details.rowIndex + 1) + ".png");

Code to integrate with the Remove Background API

function processSpreadsheet() {
  // Config variables
  const apiKey = "REPLACE_WITH_YOUR_API_KEY"; // Replace with your API Key
  const folder = DriveApp.getFolderById("REPLACE_WITH_GOOGLE_DRIVE_FOLDER_ID"); // Replace with your Google Drive folder ID

  const IMAGE_URL_COL = 0;
  const STATUS_COL = 1;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  data.forEach(function (row, index) {
    if (index == 0 || row[STATUS_COL] === 'Processed') return; // Skip header row and processed rows

    var imageUrl = row[IMAGE_URL_COL];

    // Download image
    var imageResponse = UrlFetchApp.fetch(imageUrl);
    var imageBlob = imageResponse.getBlob();

    // Prepare multipart form data
    var formData = {
      'image_file': imageBlob
    };

    var options = {
      'method' : 'post',
      'headers': { 'x-api-key': apiKey },
      'muteHttpExceptions': true,
      'payload': formData
    };

    var apiEndpoint = "https://sdk.photoroom.com/v1/segment";
    
    try {
      var response = UrlFetchApp.fetch(apiEndpoint, options);
      if (response.getResponseCode() == 200) {
        var resultBlob = response.getBlob();
        resultBlob.setName((index + 1) + ".png");
        folder.createFile(resultBlob);
        sheet.getRange(index + 1, STATUS_COL + 1).setValue('Processed');
      } else {
        sheet.getRange(index + 1, STATUS_COL + 1).setValue('Error');
        Logger.log("Error processing row " + (index + 1) + ": " + response.getContentText());
      }
    } catch (e) {
      sheet.getRange(index + 1, STATUS_COL + 1).setValue('Error');
      Logger.log("Error processing row " + (index + 1) + ": " + e.message);
    }

    SpreadsheetApp.flush();
  });
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Create a custom menu in the Google Sheets UI.
  ui.createMenu('Photoroom Actions')
      .addItem('Process through Photoroom API', 'processSpreadsheet')
      .addToUi();
}

There's quite a bit of code. Fortunately, you will only need to configure a few parts:

1. update const apiKey with your Photoroom apiKey

If you don't have an apiKey, here are the steps to create yours.

1. update const folder with the id of the Google Drive folder where you want to save the result images

To get the id of a Google Drive folder, you just need to look at the folder's URL:

https://drive.google.com/drive/u/0/folders/THIS_IS_THE_FOLDER_ID

That's all the changes you need to make, the rest of the code can be used as-is.

By default, the result image will be named using their respective line number in the spreadsheet. If needed you can update this behavior by changing this line:

blob.setName((index + 1) + ".png");

Step 2: Running a Google Apps Script

Finally, the last step is to run the script!

The first time you run the script, we recommend you do it inside the development environment, using the button Run:

By running this script inside the development environment, you'll have access to its console output, which should help in case there are any errors.

The first time you run the script, you'll be prompted to give it permission to access your Google Sheets and Google Drive.

Once the script has finished running, you can go back to the spreadsheet, where you should see that all the rows are now marked as Processed:

The Google Drive folder whose id you used should also contain the result images:

You should also notice that the spreadsheet has gained a new menu called Photoroom Actions:

This menu was added thanks to the code inside the function onOpen().

It is a shortcut to execute the function processSpreadsheet() directly within the spreadsheet.

If the menu PhotoRoom Actions doesn't appear, try refreshing the webpage

This shortcut will be useful if you add more images to the spreadsheet and want to process them rapidly.

Conclusion

In this tutorial, we saw how to use a Google Apps Script to easily integrate Google Sheets and Google Drive with the Photoroom API.

It's worth noting that a very similar approach can also be used in the situation where your images are already stored in Google Drive, using a standalone Google Apps Script.