使用Google Apps脚本将图像从Google云端硬盘或URL插入Google表格中 [英] Inserting an Image From Google Drive or from a URL into Google Sheets with Google Apps Script

问题描述

我在下面使用此脚本将URL中的图像插入Google表格中的指定单元格中，但是，它不适用于Google云端硬盘中包含的图像URL.

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

用户在一个选项卡上以预定义数量的单元格输入图像的URL.根据它们选择的顺序(可以有1到5张图像)，这些图像将插入到另一个选项卡上的指定单元格中.我该如何重写上面的代码，以解决这两个URL( https://google.com/image. png )，然后像这样( https://drive.google.com/open? id = 12Au6IQE9l9X_hzM5n87Fs9gajJ )?

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

谢谢！

推荐答案

• 您想使用IMAGE()的公式将Google云端硬盘中的图像文件放入电子表格.
  • 在您的情况下，URL类似于https://google.com/image.png和https://drive.google.com/open?id=###.
  • https://google.com/image.png是外部链接.
  • https://drive.google.com/open?id=###是您的Google云端硬盘中的链接.
  • 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.

    如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

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

    不幸的是，无法使用IMAGE()将Google云端硬盘中的图像文件直接放入电子表格.在这种情况下，需要公开共享图像文件.因此，在此答案中，文件被公开共享并放置到电子表格中.

    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.

    修改脚本后，请进行以下修改.

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

    • 在示例中，如果文件的所有者与您不同，则文件将被复制到Google云端硬盘的根文件夹中.当您要放置特定文件夹时，请修改DriveApp.getRootFolder().
    • 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().
    • 如果包括除https://drive.google.com/open?id=###模式之外的URL，请显示示例URL.
    • If the URLs except for the pattern of https://drive.google.com/open?id=### are included, please show the sample URLs.
    • setSharing(accessType，PermissionType)

    请按如下所示修改脚本中的for循环.

    Please modify the for loop in your script as follows.

    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)更改单元格大小.
    • 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.

    这篇关于使用Google Apps脚本将图像从Google云端硬盘或URL插入Google表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！