How to get an "Image in cell" URL from the Google Sheets API

9
votes

I use sheets.spreadsheets.values.get to get information inside a Spreadsheet, but the cells that contain images show an empty string value.

I tried using values:userEnteredValue as a parameter for the request (as shown in this question Retrieve images in cells using Google Sheets API), but that didn't work.

sheets.spreadsheets.values.get({
      spreadsheetId: 'XXX',
      range: 'firstSheet!A1:E10',
      key: "XXXX"
      //values: 'userEnteredValue'
}

This is what I currently get and have enter image description here

EDIT: I realized one of my problems was that I used spreadsheets.values.get when I should be using spreadsheets.get. I have changed this, but I still cant manage to get the url

enter image description here

The cell that contains text has a userEnteredValue property, but the one that contains the image does not.

1
node.jsgoogle-sheetsgoogle-sheets-api
imgur.com/a/aQBcxjn using the fields parameter returns an empty object for the image – Bruno Puccio
From your question, I couldn't understand about your Spreadsheet. In order to correctly understand about your situation, can you provide a sample Spreadsheet and script? Of course, please remove your personal information. – Tanaike
[@Tanaike], you can place an image in a spreadsheet cell with something like =image("https://www.google.com/images/srpr/logo3w.png"), but sheets.spreadsheets.values.get() returns empty string for that cell. – Alex Baban
@Alex Baban Thank you for your comment. I proposed a modified script as an answer. Could you please confirm it? – Tanaike
@Tanaike, your answer works, I voted up – Alex Baban

1 Answers

11
votes
  • You want to retrieve URL from =image("https://www.google.com/images/srpr/logo3w.png") in a cell.
  • You want to use the method of sheets.spreadsheets.values.get().
  • You want to achieve this using googleapis of Node.js.
    • You have already been able to use Sheets API.

If my understanding is correct, how about this modification?

Modification point:

  • =image("https://www.google.com/images/srpr/logo3w.png") is put to a cell as a formula. So in order to retrieve the formula, please use valueRenderOption: "FORMULA".

Modified script:

This modified script supposes that =image("https://www.google.com/images/srpr/logo3w.png") is put in a cell "A1" of a sheet name of "Sheet1".

sheets.spreadsheets.values.get(
  {
    spreadsheetId: "###",
    range: "Sheet1!A1",
    valueRenderOption: "FORMULA"
  },
  function(err, res) {
    if (err) {
      console.log(err);
      return;
    }
    const url = res.data.values[0][0].match(/https?:\/\/[\w\S][^"]+/)[0];
    console.log(url);
  }
);

Result:

https://www.google.com/images/srpr/logo3w.png

Reference:

If I misunderstood your question and that was not the result you want, I apologize.

Edit:

  • You want to retrieve the URL of image which inserted in a cell and on a sheet by "the INSERT option in spreadsheet".

If my understanding is correct, unfortunately, in the current stage, when an image is inserted in a cell from an URL by "the INSERT option in spreadsheet", the URL of image cannot be retrieved by Sheets API and Spreadsheet Service. Also when an image is inserted on a sheet from an URL by "the INSERT option in spreadsheet", the URL of image cannot be retrieved by Sheets API and Spreadsheet Service. I apologize for this situation.

Reference: