Inserting an Image From Google Drive or from a URL into Google Sheets with Google Apps Script

3
votes

I was using this script below to insert images from a URL into specified cells in Google Sheets, however, it won't work for image URLs contained within Google Drive.

//If any graphic is a URL, insert that URL into the corresponding cell in the Briefing tab.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName("main_gen").getRange("ImageRange").getValues();
  var dstSheet = ss.getSheetByName("Briefing");
  for (var num = 0; num < source.length; num ++) {
    if (/https?:\/\//.test(source[num][0])) { //Check to ensure a URL has been entered.
      var graphicformula = '=IMAGE("' + source[num][0] + '",1)';
      dstSheet.getRange(graphics_placements[num]).setFormula(graphicformula);
    }
  }

The user enters the URL of images in a predefined number of cells on one tab. Based on the order they select (there can be anywhere from 1 to 5 images), the images are inserted in specified cells on another tab. How could I rewrite the above code to account for both a URL like this (https://google.com/image.png) and like this (https://drive.google.com/open?id=12Au6IQE9l9X_hzM5n87Fs9gajJ)?

Thanks!

1
google-apps-script
I can understand about the URL of https://drive.google.com/open?id=###. But I cannot understand about the URL of https://google.com/image.png. So can I ask you about it? For example, can you provide the sample URL of https://google.com/image.png? – Tanaike
@Tanaike It could be any image from online such as: neilpatel.com/wp-content/uploads/2019/08/google.jpg. – hunter21188
Thank you for replying. In that case, the URL can be used for =IMAGE(). In your question, you want to use such URL and the URL of https://drive.google.com/open?id=###. Is my understanding correct? – Tanaike
Correct. I need a script that will account for either one of those URLs. – hunter21188
Thank you for replying. I thought that I could correctly understand about your question. So I proposed a modified script as an answer. Could you please confirm it? If that was not the result you want, I apologize. – Tanaike

1 Answers

5
votes
  • You want to put the image files in your Google Drive to the Spreadsheet using the formula of IMAGE().
    • In your case, the URL is like https://google.com/image.png and https://drive.google.com/open?id=###.
    • https://google.com/image.png is the external link.
    • https://drive.google.com/open?id=### is the link in your Google Drive.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, the image files in the Google Drive cannot be directly put to the Spreadsheet using IMAGE(). In this case, the image files are required to be publicly shared. So in this answer, the files are publicly shared and put to the Spreadsheet.

Modified script:

When your script is modified, please modify as follows.

for (var num = 0; num < source.length; num ++) {
  if (/https?:\/\//.test(source[num][0])) { //Check to ensure a URL has been entered.
    var graphicformula = '=IMAGE("' + source[num][0] + '",1)';
    dstSheet.getRange(graphics_placements[num]).setFormula(graphicformula);
  }
}

for (var num = 0; num < source.length; num ++) {
  if (/https?:\/\//.test(source[num][0])) { //Check to ensure a URL has been entered.
    var res = source[num][0].match(/drive\.google\.com\/open\?id=(\w.+)|drive\.google\.com\/file\/d\/(\w.+)\//);
    if (res && res.length > 0) {
      var id = res[1] || res[2];
      var file = DriveApp.getFileById(id);
      if (file.getOwner().getEmail() != Session.getActiveUser().getEmail()) {
        file = file.makeCopy(DriveApp.getRootFolder());
      }
      file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      source[num][0] = "https://drive.google.com/uc?export=download&id=" + id;
    }
    var graphicformula = '=IMAGE("' + source[num][0] + '",1)';
    dstSheet.getRange(dstSheet.getLastRow() + 1, 1).setFormula(graphicformula);
  }
}
  • In the sample, when the owner of the file is different from you, the file is copied to the root folder of your Google Drive. When you want to put the specific folder, please modify DriveApp.getRootFolder().

Note:

  • If the URLs except for the pattern of https://drive.google.com/open?id=### are included, please show the sample URLs.

Reference:

Added:

Please modify the for loop in your script as follows.

Modified script:

var scale = 0.5; // In this case, the imported image is reduces with 50 % from the original size.
var n = 0;
var cellWidth = 0;
for (var num = 0; num < source.length; num ++) {
  if (/https?:\/\//.test(source[num][0])) { //Check to ensure a URL has been entered.
    var res = source[num][0].match(/drive\.google\.com\/open\?id=(\w.+)|drive\.google\.com\/file\/d\/(\w.+)\//);
    var blob = res && res.length > 0 ? DriveApp.getFileById(res[1] || res[2]).getBlob() : UrlFetchApp.fetch(source[num][0]).getBlob();
    var lastRow = dstSheet.getLastRow() + 1 + n++;
    var image = dstSheet.insertImage(blob, 1, lastRow);
    var imageWidth = image.getWidth() * scale;
    var imageHeight = image.getHeight() * scale;
    cellWidth = cellWidth > imageWidth ? cellWidth : imageWidth;
    image.setWidth(imageWidth);
    image.setHeight(imageHeight);
    dstSheet.setRowHeight(lastRow, imageHeight);
  }
}
dstSheet.setColumnWidth(1, cellWidth);
  • In this case, the image is put on the cell. This is put not in the cell. So when you want to match the image size to the cell size, the cell size is required to be changed. In above script, dstSheet.setRowHeight(lastRow, imageHeight) and dstSheet.setColumnWidth(1, cellWidth) change the cell size.