Retrieve images in cells using Google Sheets API

Question

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?

Output

Answer 1

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\")"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

International

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? – Output

Authorization isn't required if the spreadsheet is shared to "Anyone with the link." – International

"{ "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 sheets.googleapis.com/v4/spreadsheets/… – Neoterize

This no longer works. It returns an empty string – Nimmons

Any Other API is Available for Retrieving the Image from Google spreadsheet??? – Twinscrew

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() – Motorboating

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. – Pileum

@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. – Knave

This doesn't seem to work if the image was added via the Insert -> Image menu. It's just blank – Raving