1

I’m trying to paste png images from my drive into a spreadsheet. Lots of articles say that insertImage() on Sheet Class will achieve this but when I run the code below, I keep getting a Service error where insertImage() is called. I googled it and looked it up here as well but haven’t been able to find the solution yet. What am I doing it wrong?

 function pastePhotosFromDrive(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("画像");

 var folderId = "XXXXXXXXXXXXXXXXXXXXXXXXXXX";
 var files = DriveApp.getFolderById(folderId).getFiles();
 var row = 1;
 var col = 1;

 Logger.log(files);
//  var file1 = files.next();
//  var blob1 = file1.getBlob());

 while(files.hasNext()){
   var file = files.next();
   var blob = file.getBlob();
   sheet.insertImage(blob, col, row);

   row += 3;

   if(row >= 25){
     col = 0;
     row += 20;
   }
 } 
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Yuko Kasahara
  • 113
  • 8

1 Answers1

2

How about this answer?

Issue and workaround:

I thought that in your situation, it is considered that the following 2 situations might occur.

  1. The file is not the image file.
  2. The image size might be over the maximum size. At the Spreadsheet, when the area of image is larger than 1,048,576 pixels^2, an error occurs. Ref1 and Ref2.

As one of several workarounds, I would like to propose to modify the size of the image by selecting the image file.

Modified script:

When your script is modified, please modify as follows.

Before you use this script, please enable Drive API at Advanced Google Services.

From:

while(files.hasNext()){
  var file = files.next();
  var blob = file.getBlob();
  sheet.insertImage(blob, col, row);

  row += 3;

  if(row >= 25){
    col = 0;
    row += 20;
  }
}

To:

while(files.hasNext()){
  var file = files.next();

  if (file.getMimeType().indexOf("image") > -1) { // Here, the image file is selected.
    var blob;
    var obj = Drive.Files.get(file.getId());
    var imageMetadata = obj.imageMediaMetadata;
    if (imageMetadata.height * imageMetadata.width > 1048576) {
      var width = 500; // Here, as a test case, 500 pixels is used for the width.
      var tlink = obj.thumbnailLink.replace(/=s\d+/, "=s" + width);
      blob = UrlFetchApp.fetch(tlink).getBlob();
    } else {
      blob = file.getBlob();
    }

    sheet.insertImage(blob, col, row);

    row += 3;

    if(row >= 25){
      col = 0;
      row += 20;
    }
  }
}
  • At this modified script, the image file is selected, and when the image size is over the maximum limitation, the size is made smaller. Then, the modified image is inserted.
    • Here, as a test case, 500 pixels is used for the modified width.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165