Inserting Image into Google Sheets from Google Drive using Apps Script

0
votes

I have been trying for 9 days to add an image that is uploaded to my drive (via the use of a google form) into my Google sheet using Apps Script, but it isn't working and I have no idea why, this is my code below:

function getImage(){

  var folderImage = DriveApp.getFolderById("0B7gxdApLS0TYfm1pRHpHSG4yTm96bm1PbTZQc1VmdGpxajY4N1J4M1gtR1BiZ0lOSl9NMjQ");
      Logger.log(folderImage.getFiles().next().setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW));


  Logger.log(folderImage.getFiles().next().getSharingAccess());
  Logger.log(folderImage.getFiles().next().getSharingPermission());

  var imageFile = folderImage.getFiles().next().getBlob();

  detailSheet.insertImage(imageFile, 1, 13);

}

I have even tried making the sharing and access permissions of the to be as open as possible but I keep getting this error message:

"We're sorry, a server error occurred. Please wait a bit and try again"

I find the error message ambiguous which leads me at a dead end. Usually the message gives me a good idea of where I have gone wrong.

I believe my code is correct, and during my research I have found no definitive reason this shouldn't work. Does anybody know where I am going wrong?

A solution would be great but preferably a critique on my code so I can learn :)

1
google-apps-scriptgoogle-sheetsgoogle-forms
Use a different Spreadsheet(a new one). – TheMaster
Make sure the blob you are using is the correct mimetype. – tehhowch

1 Answers

0
votes

Couple of issues with your script:

  1. You never bind to a specific file, so to work with the same file you have to reinitialize the iterator each time.
  2. You don't verify its mimetype prior to using it as an image

An example that resolves those issues:

function addFolderPNGs_(sheet, folderId) {
  const folder = folderId ? DriveApp.getFolderById(folderId) : DriveApp.getRootFolder(); // scope only to the root or given folder.
  const imgs = folder.getFilesByType(MimeType.PNG);
  var targetRow = sheet.getLastRow();
  while (imgs.hasNext()) {
    var img = imgs.next();
    Logger.log(img.getName())
    sheet.insertImage(img.getBlob(), 1, ++targetRow);
  }
}

References