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

Question

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

---

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

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

Tingly

imgur.com/a/aQBcxjn using the fields parameter returns an empty object for the image – Tingly

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

[@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. – Centenary

@Alex Baban Thank you for your comment. I proposed a modified script as an answer. Could you please confirm it? – Maidstone

@Tanaike, your answer works, I voted up – Centenary

@Alex Baban Thank you for replying. By your comment, I could understand about the issue. Thank you, too. – Maidstone

Has anyone figured out how to get this to with with images that are inserted and not added with the =image() formula? It still remains blank when using Insert. – Homology

Answer 1

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

• spreadsheets.values.get

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:

• Spreadsheet Service

Maidstone

this works great using the =image() formula but is it possible to get the url of an image uploaded using the INSERT option in spreadsheet? when i do this the image is shown on the cell but the formula is empty – Tingly

@Bruno Puccio Thank you for replying. I updated my answer. Could you please confirm it? If I misunderstood your question, please tell me. – Maidstone

Has anyone figured out how to get this to with with images that are inserted and not added with the =image() formula? It still remains blank when using Insert. – Homology

@Mr. BigglesWorth About your question of Has anyone figured out how to get this to with with images that are inserted and not added with the =image() formula? It still remains blank when using Insert., I would like to support you. But I cannot correctly imagine your question. So can you post it as a new question with detailed information? By this, it will help users including me think of the solution. If you can cooperate to resolve your question, I'm glad. Can you cooperate to do it? – Maidstone