# How to process images from Google Sheets

{% embed url="<https://www.youtube.com/watch?v=P0N2PJs-T_w>" %}

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](https://docs.photoroom.com/image-editing-api-plus-plan) or the [Remove Background API](https://docs.photoroom.com/remove-background-api-basic-plan) with Google Sheets.

Let's take the example of this spreadsheet:

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2F4c9TP3rZrzXIR3qFxpK6%2FScreenshot%202023-12-07%20at%2011.40.25.png?alt=media&#x26;token=8abc1224-ec88-4344-93df-befd989743bd" alt=""><figcaption></figcaption></figure>

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*:

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FOx1rGH27khYq8irrsT4x%2FScreenshot%202023-12-07%20at%2011.40.49.png?alt=media&#x26;token=bf9741e4-6dbf-4919-a35c-fe3873326e87" alt=""><figcaption></figcaption></figure>

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

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FvUOJ4nfncgWdB0gDGFTl%2FScreenshot%202023-12-07%20at%2010.55.09.png?alt=media&#x26;token=23c6dfe2-e8e2-46a7-842c-8fe429482d3f" alt="" width="563"><figcaption></figcaption></figure>

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*:

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FLdgRrX14yiL66ZJczuLf%2FScreenshot%202023-12-07%20at%2010.54.38.png?alt=media&#x26;token=94c4a507-e970-4013-897a-b1dbb81b2576" alt="" width="563"><figcaption></figcaption></figure>

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

<div><figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FhoOUuP7Wd3WUNpEoeeJO%2FScreenshot%202023-12-07%20at%2010.57.27.png?alt=media&#x26;token=33606beb-5384-458b-9901-fc1b2cdb3b61" alt=""><figcaption></figcaption></figure> <figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2F88GsyI69UlU8P9pUvUSl%2FScreenshot%202023-12-07%20at%2010.57.37.png?alt=media&#x26;token=3577bc95-f6c9-4ae4-bbc9-4e28ef58756d" alt=""><figcaption></figcaption></figure></div>

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

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FKoh9QGD4qArsvc8hGL6R%2FScreenshot%202023-12-07%20at%2010.58.18.png?alt=media&#x26;token=4f363909-703d-4649-87b5-7e0d6294453e" alt=""><figcaption></figcaption></figure>

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:

{% tabs %}
{% tab title="Code to integrate with the Image Editing API" %}

```javascript
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

{% hint style="info" %}
If you don't have an API key, here are the [steps to create yours](https://docs.photoroom.com/getting-started/introduction#how-can-i-get-my-api-key).
{% endhint %}

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

{% hint style="info" %}
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](https://drive.google.com/drive/u/0/folders/10s7jyIaTzIyWHClYa4QfxgzYQeXJaRcV)
{% endhint %}

3. 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%

{% hint style="info" %}
The full list of supported parameters is available in the [API reference](https://www.photoroom.com/api/docs/reference/87e1c15260b1f-beta-edit-image-v2).

You can visually define the edits you want to apply using the [API playground](https://www.photoroom.com/api/playground).
{% endhint %}

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

{% hint style="info" %}
By default, the result images will be named using their respective line number in the spreadsheet. If needed you can modify this behavior by changing this line:

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

{% endhint %}
{% endtab %}

{% tab title="Code to integrate with the Remove Background API" %}

```javascript
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

{% hint style="info" %}
If you don't have an API key, here are the [steps to create yours](https://docs.photoroom.com/getting-started/introduction#how-can-i-get-my-api-key).
{% endhint %}

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

{% hint style="info" %}
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](https://drive.google.com/drive/u/0/folders/10s7jyIaTzIyWHClYa4QfxgzYQeXJaRcV)
{% endhint %}

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

{% hint style="info" %}
By default, the result images will be named using their respective line number in the spreadsheet. If needed you can modify this behavior by changing this line:

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

{% endhint %}
{% endtab %}
{% endtabs %}

## 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*:

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FgSqYawnAx29PfW9VLK8B%2FScreenshot%202023-12-07%20at%2012.24.49.png?alt=media&#x26;token=65b8c378-154d-41c3-9672-a1c6fc1efaef" alt="" width="563"><figcaption></figcaption></figure>

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.

{% hint style="info" %}
The first time you run the script, you'll be prompted to give it permission to access your Google Sheets and Google Drive.
{% endhint %}

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*:

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2F2ThizgUhCrkjFbWpbrsZ%2FScreenshot%202023-12-07%20at%2012.31.22.png?alt=media&#x26;token=b625b9df-4d37-4411-9435-c66b6e917a4d" alt="" width="563"><figcaption></figcaption></figure>

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

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FUWLMmOPmNW4rQkSotHF3%2FScreenshot%202023-12-07%20at%2012.31.46.png?alt=media&#x26;token=a88ceee1-5cfb-469f-93b5-afc632ee25fb" alt="" width="563"><figcaption></figcaption></figure>

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

<figure><img src="https://2855892273-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1SYxn7dWbQYsNtUdJE3f%2Fuploads%2FwlNiXQrV79Tr8L3A75Mx%2FScreenshot%202023-12-07%20at%2012.33.41.png?alt=media&#x26;token=f3e8a0ed-7cd4-4ffc-815f-8c46eec89339" alt="" width="563"><figcaption></figcaption></figure>

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.

{% hint style="info" %}
If the menu *Photoroom Actions* doesn't appear, try refreshing the webpage
{% endhint %}

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](https://docs.photoroom.com/integrations/how-to-process-images-from-google-drive).