1

I'm tryna create a google app script code that will retrieve image files from a folder and insert them into cells in a column.

All sheets within this spreadsheet have the same template, so all getRange() rows and columns are correct. All images within all folders that I'm trying to retrieve images from have standardised dimensions of ‪1,202 x 720‬ pixels (98KB), are in JPG format, and its names are just Slide1.JPG, Slide2.JPG, etc.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function insertImgsFromDriveFolder() {
  Logger.log('sheet name: ' + sheet.getSheetName());
  Logger.log('Drive Folder ID cell: ' + sheet.getRange(1,4).getA1Notation());
  var folderID = sheet.getRange(1,4).getValue();
  Logger.log('Drive Folder ID: ' + folderID);
  var folder = DriveApp.getFolderById(folderID);

  var files = folder.getFiles(); // retrieves Iteration of files, sometimes in reverse order

  // so must get count of no items in folder
  var count = 0;
  while (files.hasNext()) {
    var file = files.next();
    count++;
  }

  // then search for each item by name + display in sheet
  for (let i=1; i <= count; i++) {
    var imgFile = folder.getFilesByName("Slide" + i + ".JPG").next();
    //imgFile.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);
    Logger.log('filename: ' +imgFile);
    img = SpreadsheetApp.newCellImage().setSourceUrl('https://drive.google.com/uc?export=view&id=' + imgFile.getId()).build();
    Logger.log('file: ' +img);
    sheet.getRange(i+4,2).setValue(img);
  }
}

The error message I receive in the Execution log is "Exception: Error retrieving image from URL or bad URL: " on the sheet.getRange(i+4,2).setValue(img); line.

I am able to view the image by copying that URL from the error in Chrome's Incognito Mode (no google account signed in), so I think the file is publicly-accessible. I am also able to manually insert an image into a cell using that URL using =IMAGE(). De-commenting imgFile.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT); to force it to be public-accessible results in the same error.

I also think that the file size is not an issue as it smaller than the size limit stated in this post.

I also do not want to use insertImage() as I want the images to be inserted into the cells. I have also tried the solutions provided in here, here and here.

Lmk what I can try to fix this issue! Also, if you also have a solution on how to force the FileIteration to take files in alphabetical/date-created order that would be awesome.

Thanks in advance!

Improve this question
7
  • I think that there are 2 questions in your question. 1. You want to remove the error of Exception: Error retrieving image from URL or bad URL. 2. You want to retrieve the image files in a specific order. I think that I can understand your 1st question. But, unfortunately, I cannot understand your 2nd question. I cannot understand take files in alphabetical/date-created order. Can I ask you about the detail of your 2nd question? First, I would like to correctly understand your questions. Commented May 22, 2023 at 7:28
  • Hi there! (1.) is my main qn. (2.) is just a side qn that could help me improve the efficiency For (2.), I want to retrieve image files in the following order: Slide1.JPG, then Slide2.JPG, then Slide3.JPG, and so on. Is there a way I can specify which order folder.getFiles(); to retrieves the files? whether that order is by ascending/descending alphabetical order, or furthest/latest date creation, etc. Alternatively, is there a way I can sort the files in a folder prior to using folder.getFiles();? Commented May 22, 2023 at 15:24
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't understand about take files in alphabetical/date-created order. Can I ask you about the detail of it? Commented May 23, 2023 at 0:05
  • No worries, thanks for helping me out anyways! For example, if my folder contained the files {Slide4.JPG, Slide2.JPG, Slide5.JPG, Slide1.JPG, Slide3.JPG}, I would want to get them such that the numbers start from the smallest, then get the next smallest, and so on such that I get {Slide1.JPG, Slide2.JPG, Slide3.JPG, Slide4.JPG, Slide5.JPG} Commented May 23, 2023 at 2:14
  • Thank you for replying. About take files in alphabetical/date-created order, do you want to retrieve the files with the ascending order of filename? Or, do you want to retrieve the files with the ascending order of the created date of the file? Or, do you want to sort the file list by the created date after the file list was sorted by the filename? I cannot understand which do you want to do. I apologize for my poor English skill again. Commented May 23, 2023 at 2:17

1 Answer 1

Reset to default
0

From your comments, I believe your goal is as follows.

  • You want to retrieve the image files by the filename like Slide1.JPG, Slide2.JPG, Slide3.JPG, Slide4.JPG, Slide5.JPG,,, in order, and want to put them to the cells "B5:B" using Google Apps Script.
  • The format of filename is constant like Slide1.JPG, Slide2.JPG, Slide3.JPG, Slide4.JPG, Slide5.JPG,,,.

Modification points:

  • In the current stage the images can be put into multiple cells using setValues.
  • In your script, 2 loops are used. I thought that a single loop can be used.
  • From your error message Exception: Error retrieving image from URL or bad URL:, in this case, as a workaround, I would like to propose putting the images as the data URL.
  • About the sort of files, how about

When these points are reflected in your script, it becomes as follows.

Modified script:

function insertImgsFromDriveFolder() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  var folderID = sheet.getRange(1, 4).getValue();
  var folder = DriveApp.getFolderById(folderID);
  var files = folder.searchFiles("title contains 'Slide' and mimeType contains 'image' and trashed=false");
  var ar = [];
  while (files.hasNext()) {
    var file = files.next();
    var filename = file.getName();
    var mimeType = file.getMimeType();
    if ((/Slide\d*\.JPG/i).test(filename)) {
      var base64 = Utilities.base64Encode(file.getBlob().getBytes());
      var img = SpreadsheetApp.newCellImage().setSourceUrl(`data:${mimeType};base64,${base64}`).build();
      ar.push({ filename, createdDate: file.getDateCreated(), img });
    } else {
      console.log(filename); // If the file is not your expected file, you can see them in the log.
    }
  }
  if (ar.length == 0) return;
  ar.sort((a, b) => Number(a.filename.match(/Slide(\d*)\.JPG/i)[1]) < Number(b.filename.match(/Slide(\d*)\.JPG/i)[1]) ? -1 : 1);
  sheet.getRange(5, 2, ar.length).setValues(ar.map(({ img }) => [img]));
}
  • When this script is run, the images are retrieved and converted to the data URL. And, the data URLs are put into the cells "B5:B" of the active sheet as the images.
  • In order to retrieve the files, I used the search query of title contains 'Slide' and mimeType contains 'image' and trashed=false.

Note:

  • In this script, it supposes that your folder ID and the image files are valid. Please be careful about this.

References:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yup this is exactly what I wanted! Thanks so much :)
ok i think i accepted it!
@Bruce Dylan Tan Thank you for your response. I'm glad your issue was resolved. Thank you, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.