Getting image url in google script

Question

Trying to get image url in google script. couldn’t find any function that is able to get the url from images that are not in a specific cell, images are located above the grid. any ideas?

Answer 1

Issue and workaround:

On October 30, 2018, in order to manage the images on the cells in Spreadsheet, a new Class of OverGridImage has been added. Ref By this, the images on the cells got to be able to be managed. This class has the method of getUrl. The official document of this method says as follows.

Gets the image's source URL; returns null if the URL is unavailable. If the image was inserted by URL using an API, this method returns the URL provided during image insertion.

Namely, for example, when the following script is run, the URL of the image can be retrieved.

function sample1() {
  const sheet = SpreadsheetApp.getActiveSheet();
  
  // Put image from URL.
  sheet.insertImage("https://stackoverflow.design/assets/img/logos/so/logo-stackoverflow.png", 1, 1);

  // Retrieve image URL.
  const images = sheet.getImages();
  const url = images[0].getUrl();
  console.log(url)
}

In your actual situation, if your images are put on the cells using the above script, the URLs can be retrieved by the above simple script. But, here, there is an important point. After the image was put using this script, when you manually move the image, the URL cannot be retrieved. I think that this is a bug.

And also, if you had manually put the images from the URL and your drive, unfortunately, the URL of the images cannot be retrieved. About this, it has already been reported to the Google issue tracker. Ref

If you had manually put the images from the URL and your drive, and when you want to retrieve the URLs of the images, it is required to use a workaround. In this case, I would like to propose to use this method of this answer. In this answer, my created Google Apps Script library is used.

Usage:

1. Install Google Apps Script library.

You can see the method for installing this library at here.

2. Enable Drive API.

In this case, Drive API is used. So, please enable Drive API at Advanced Google services.

3. Sample script.

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const images = sheet.getImages();
  const obj = images.reduce((o, e) => {
    const u = e.getUrl();
    if (u) o[e.getAnchorCell().getA1Notation()] = u;
    return o;
  }, {});
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId()).getSheetByName(sheetName).getImages();
  if (res.length == 0) return;
  const urls = res.map(({ image, range }, i) => {
    if (obj[range.a1Notation]) return obj[range.a1Notation];

    const o = Drive.Files.insert({ title: `sample${i + 1}` }, image.blob);
    const url = o.thumbnailLink.replace(/\=s\d+/, "=s1000");
    DriveApp.getFileById(o.id).setTrashed(true);
    return url;
  });
  console.log(urls)
}

4. Testing.

When the above script is run, the images are retrieved from "Sheet1" and retrieve the URLs of the images. For example, when there are images put with the image URL using the script, the URL can be retrieved.

Note:

• In this workaround, in order to retrieve the URL of the image, the thumbnail link is used. This link is not permanent. Please be careful about this. If you are required to retrieve the permanent link, please create the retrieved image file blob as the file, and please publicly share them, and then, please retrieve the WebContentLink. By this, you can retrieve the permanent link of the image.

References:

• DocsServiceApp
• Related thread
  • How to access new 'in-cell-image' from google apps script?