Retrieve images in cells using Google Sheets API

Adam picture Adam · Sep 14, 2016 · Viewed 8.6k times · Source

I'm using the Google Sheets API to return the JSON of a Google Sheet,

e.g

 var url = 'https://spreadsheets.google.com/feeds/list/' + id + '/od6/public/values?alt=json';

In one of the cells I have added an image, but this appears empty in the json.
How can I retrieve this image?

Answer

Eric Koleda picture Eric Koleda · Sep 14, 2016

This is possible if you use Sheets API v4. If you make a spreadsheets.get request you get access to the =IMAGE(...) formula used in the cell.

GET https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}?
    includeGridData=true&ranges={RANGE_A1}&
    fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue

200 OK

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "formulaValue": "=image(\"https://www.google.com/images/srpr/logo3w.png\")"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}