google apps script upload picture into cell to google spreadshteet

Question

i was following this article and made my appscript that worked perfectly. The picture from google drive was succesfully uploaded to the spredsheet INTO the cell.

My script worked for like 3 months with no problem and suddenly it stoped working. with error Exception: Error during upload picture from URL address or address is not valid: "https://lh3.googleusercontent.com/drive-storage/......"

Is there anything that happend in appscript that script will stop working?

this is the actual code

function myFunction() {
  const imageFileId = "fileIDhere"; // Please set the file ID of the image on Google Drive.

  // Prepare image.
  const url = Drive.Files.get(imageFileId).thumbnailLink.replace(
    /\=s.+/,
    "=s512"
  ); // When =s512 is changed to =s1000, the width of the image becomes 1000 pixels.
  const image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);
}

Thank you

Answer 1

I also have the same situation. First, I noticed that the thumbnail link retrieved by Drive API was not a public link. I thought that this was the reason for this issue. But, now, it seems that in the current stage, the thumbnail link retrieved by Drive API cannot be directly used as the source URL of CellImageBuilder while the thumbnail link is a public link. I think that a little change might have occurred. I think that this is due to the change of specification on the Google side. But, I'm not sure whether this is the temporal situation.

So, in the current workaround, how about the following modification? In this case, the image is put into a cell using the data URL instead of the public link.

Modified script 1:

In this modification, the image blob is directly used from imageFileId.

function myFunction() {
  const imageFileId = "###"; // Please set the file ID of the image on Google Drive.

  // Prepare image.
  const file = DriveApp.getFileById(imageFileId);
  const base64 = Utilities.base64Encode(file.getBlob().getBytes());
  const image = SpreadsheetApp.newCellImage().setSourceUrl(`data:${file.getMimeType()};base64,${base64}`).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);
}

Modified script 2:

In this modification, the image blob from the thumbnail link from imageFileId is used. If your image size is large, this pattern might be useful.

function myFunction() {
  const imageFileId = "###"; // Please set the file ID of the image on Google Drive.

  // Prepare image.
  const url = `https://drive.google.com/thumbnail?sz=w512&id=${imageFileId}`; // Ref: https://stackoverflow.com/a/31504086
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  const base64 = Utilities.base64Encode(blob.getBytes());
  const image = SpreadsheetApp.newCellImage().setSourceUrl(`data:image/png;base64,${base64}`).build();

  // Put image over cells.
  SpreadsheetApp.getActiveSheet().getRange("B2").setValue(image);
}