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.

imgur.com/a/aQBcxjn using the fields parameter returns an empty object for the image — Bruno Puccio
– Bruno Puccio, Commented May 14, 2019 at 20:54
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, Commented May 14, 2019 at 22:05
[@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, Commented May 14, 2019 at 22:43
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. — Mr. BigglesWorth
– Mr. BigglesWorth, Commented Jan 16, 2022 at 14:29

Tanaike · Accepted Answer · 2019-05-16 07:29:16Z

14

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

edited May 16, 2019 at 7:29

answered May 15, 2019 at 0:49

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bruno Puccio Over a year ago

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

Tanaike Over a year ago

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

Mr. BigglesWorth Over a year ago

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.

Tanaike Over a year ago

@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?

Collectives™ on Stack Overflow

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

1 Answer 1

Modification point:

Modified script:

Result:

Reference:

Edit:

Reference:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification point:

Modified script:

Result:

Reference:

Edit:

Reference:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related