google-apps-script - Inserting an Image From Google Drive or from a URL into Google Sheets with Google Apps Script

Question

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!

标签： google-apps-script

Answer 1

• 您想使用公式将 Google Drive 中的图像文件放到电子表格中IMAGE()。
  • 在您的情况下，URL 类似于https://google.com/image.pngand https://drive.google.com/open?id=###。
  • https://google.com/image.png是外部链接。
  • https://drive.google.com/open?id=###是您 Google 云端硬盘中的链接。
• 您想使用 Google Apps 脚本实现此目的。

如果我的理解是正确的，这个答案怎么样？请认为这只是几个可能的答案之一。

问题和解决方法：

遗憾的是，Google Drive 中的图像文件无法使用IMAGE(). 在这种情况下，图像文件需要公开共享。所以在这个答案中，文件是公开共享的，并被放到电子表格中。

修改后的脚本：

当您的脚本被修改时，请进行如下修改。

从：

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);
  }
}

• 在示例中，当文件的所有者与您不同时，该文件将被复制到您的 Google Drive 的根文件夹中。当您要放置特定文件夹时，请修改DriveApp.getRootFolder().

笔记：

• 如果包含除格式之外的 URL https://drive.google.com/open?id=###，请显示示例 URL。

参考：

• 设置共享（访问类型，权限类型）

添加：

请按如下方式修改脚本中的 for 循环。

修改后的脚本：

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);

• 在这种情况下，图像被放在单元格上。这不是放在牢房里。所以当你想将图像大小与单元格大小匹配时，需要更改单元格大小。在上面的脚本中，dstSheet.setRowHeight(lastRow, imageHeight)更改dstSheet.setColumnWidth(1, cellWidth)单元格大小。