How to get an “Image in cell” URL from the Google

问题:

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.

回答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