Google Apps脚本-在= IMAGE()电子表格公式中使用云端硬盘中文件的网址 [英] Google Apps Script - Using URL of File in Drive in =IMAGE() Spreadsheet Formula

问题描述

在下面的代码中，我在驱动器中获取文件的URL，并在公式= IMAGE()中使用该URL.但是，该图像未在单元格中显示.我将正在检索的URL复制并粘贴到浏览器中，并拉出了图像文件.我还尝试输入其他网址(来自Google图片搜索)，并在单元格中显示了图片.这是我的代码片段，无法正常工作:

In the below code, I'm getting the URL of a file in my Drive and using that in the formula =IMAGE(). However, the image isn't being displayed in the cell. I copied and pasted the URL that was being retrieved into my browser and it pulls up the image file. I also tried entering a different URL (from a Google image search) and it displayed the image in the cell. Here is a snippet of my code that isn't working:

//Function to populate Packing Instructions sheet
function createPackingInstructions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var entryFormSheet = ss.getSheetByName('Entry Form');
  var packingInstructionsSheet = ss.getSheetByName('Packing Instructions');
  var poNumber = entryFormSheet.getRange(2, 2).getValue();
  var drive = DriveApp;
  var proofHorizontal = drive.getFilesByName('PO ' + poNumber + ' Proof Horizontal.png');
  var proofRange = packingInstructionsSheet.getRange(1, 7);

  Logger.log(poNumber);

  //Starts by clearing the Instructions sheet
  packingInstructionsSheet.getRange(11, 1, 30, 11).clear();
  proofRange.clearContent();

  Logger.log(proofHorizontal.hasNext());

  //Gets image file URL
  while (proofHorizontal.hasNext()) {
    var file = proofHorizontal.next();
    var proofName = file.getName();
    var proofUrl = file.getUrl();

    Logger.log(proofName);
    Logger.log(proofUrl);

    proofRange.setFormula('IMAGE("' + proofUrl + '", 1)');
  }
}

我根据

I adjusted the code based on the advice in here to use the permalink version of the URL, but it has the same behavior; it inputs the formula correctly and the URL works when entered into my browser, but the image won't display in the cell. Here is the updated code:

//Function to populate Packing Instructions sheet
function createPackingInstructions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var entryFormSheet = ss.getSheetByName('Entry Form');
  var packingInstructionsSheet = ss.getSheetByName('Packing Instructions');
  var poNumber = entryFormSheet.getRange(2, 2).getValue();
  var drive = DriveApp;
  var proofHorizontal = drive.getFilesByName('PO ' + poNumber + ' Proof Horizontal.png');
  var proofRange = packingInstructionsSheet.getRange(1, 7);
  var baseUrl = "http://drive.google.com/uc?export=view&id=";

  Logger.log(poNumber);

  //Starts by clearing the Instructions sheet
  packingInstructionsSheet.getRange(11, 1, 30, 11).clear();
  proofRange.clearContent();

  Logger.log(proofHorizontal.hasNext());

  //Gets image file URL
  while (proofHorizontal.hasNext()) {
    var file = proofHorizontal.next();
    //file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
    var proofId = file.getId();
    var proofUrl = baseUrl+proofId;

    Logger.log(proofUrl);

    proofRange.setFormula('IMAGE("' + proofUrl + '", 1)');
  }
}

推荐答案

因为我找到了可行的解决方案，所以对我自己的问题进行了处理:

Necro-ing my own questions, since I found a working solution:

我上面的第二个代码示例已经结束，但是您需要使用URL的下载"版本，而不是"view"版本.见下文:

My second code example above was close, but you need to use the "download" version of the URL, not the "view" version. See below:

var baseUrl = "https://drive.google.com/uc?export=download&id=";

//Gets image file URL
while (proofHorizontal.hasNext()) {
  var file = proofHorizontal.next();
  //This line may be necessary, depending on permissions
  //file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
  var proofId = file.getId();
  var proofUrl = baseUrl+proofId;
}

以下是一些相关信息的链接: https://blog.appsevents.com/2014/04/how-to-by-pass-google-drive-viewer-and.html

Here is a link to some info on this: https://blog.appsevents.com/2014/04/how-to-bypass-google-drive-viewer-and.html

这篇关于Google Apps脚本-在= IMAGE()电子表格公式中使用云端硬盘中文件的网址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！