8

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?

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Adam
  • 1,136
  • 8
  • 26
  • 51

1 Answers1

6

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\")"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
Eric Koleda
  • 12,420
  • 1
  • 33
  • 51
  • 1
    The examples i've seen require the user to authenticate, is there a way of doing this with the app so the user doesn't have to login? – Adam Sep 22 '16 at 10:34
  • Authorization isn't required if the spreadsheet is shared to "Anyone with the link." – Eric Koleda Sep 22 '16 at 21:16
  • 1
    "{ "error": { "code": 403, "message": "The request is missing a valid API key.", "status": "PERMISSION_DENIED" } }" I get this error. The sheet is Anyone with link can edit is shared. I tried with https://sheets.googleapis.com/v4/spreadsheets/1oolt4naDn-xhuLucWbEnm6ghcr0bWsZv2_z5blZNhW8?includeGridData=true&fields=sheets%2Fdata%2FrowData%2Fvalues%2FuserEnteredValue – Code Guy Mar 06 '20 at 10:39
  • This no longer works. It returns an empty string – Marvin Feb 28 '21 at 13:36
  • Any Other API is Available for Retrieving the Image from Google spreadsheet??? – Nikunj Chaklasiya Jun 23 '21 at 04:54
  • 1
    Yes @NikunjChaklasiya it is possible with `valueRenderOption: 'FORMULA'`. For example: `sheets.spreadsheets.values.get({ spreadsheetId: MY_SPREADSHEET_ID, range: MY_SHEET_NAME, valueRenderOption: 'FORMULA', });` Then you will see the formula text in the response, including `=image()` – Jo Sprague Jul 22 '21 at 15:35
  • 1
    You need to provide your app's API key. No auth required, just the API key that identifies your app. You can find this in the Google Cloud Console under credentials. – Jose Quesada Nov 19 '21 at 03:39
  • @JoSprague "valueRenderOption" does not seem to be a valid option for spreadsheets.get, only spreadsheets.values.get. I'm getting the json for a complete spreadsheet. – D Joyce Jan 02 '22 at 20:56