Google Apps Script - Using URL of File in Drive in

问题:

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

回答1:

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

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

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