I am trying to add images to cells by means of CellImageBuilder class. I have placed the following code:

function listFilesInFolder(folderName){
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(["Name","URL","Image"]);

//change the folder ID below to reflect your folder's ID (look in the URL when you're in your folder)
  var folder = DriveApp.getFolderById("1LeGMgGSLwKEqqplaelG8rAfclPMsJTfA");
  var contents = folder.getFiles();
  let image=[]; 

  var cnt = 0;
  var file;

  while (contents.hasNext()) {
      var file = contents.next();
      cnt  ;
        data = [
              file.getName(),
              file.getDownloadUrl(),
          ];

          sheet.appendRow(data);      
  };
  insertImageIntoCell(); //Insert the images on column C
};

function insertImageIntoCell(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = 1;
  sheet.getDataRange().getValues().forEach(url =>{
    if(url[1] == "https://drive.google.com/drive/folders/1LeGMgGSLwKEqqplaelG8rAfclPMsJTfA?usp=sharing")return row  = 1;
      let image = SpreadsheetApp.newCellImage().setSourceUrl(url[1]).setAltTextDescription('TestImage').toBuilder().build();
      SpreadsheetApp.getActive().getActiveSheet().getRange('C' row).setValue(image);
      row  = 1;
  });
}

I am however getting the following errors:

The result on the sheet is this:

Name    URL Image               
Mexico_Tasty_Logo_Final_Version_c380998f-99ed-4bc5-a209-753186bee193_130x@2x.webp   https://drive.google.com/uc?id=17qRTWezDODW4dk6qlxkJxsRk9PInJGks&export=download                    
26230406_577084895958314_472618575525892770_n.jpg   https://drive.google.com/uc?id=1KDL9WsDsgYnHYZ97Cm8LH90RZb32ySNQ&export=download                    
243057792_277087470797117_4378879295375949961_n.jpg https://drive.google.com/uc?id=1lFJDiPMERd5Qv3fRFtpJgReHHAChJX9u&export=download                    
Logotipo-Curvas-01-1.png    https://drive.google.com/uc?id=1XgZYf0sIt1lonUz30OOqCbn4gwbD7R9J&export=download                    
tortilleria-taiyari-logo.webp   https://drive.google.com/uc?id=1c1JioJmM-kKFW5zg9lCWqe12FIiddxU6&export=download                    
90632540_197306841718448_6973479905908490240_n.jpg  https://drive.google.com/uc?id=1B4kb3K_R1FfOySk8GR1IPjnX9W__EAb0&export=download    

What am I missing?

CodePudding user response：

Modification points:

• When the webContentLink like https://drive.google.com/uc?id={fileId}&export=download is used, in order to put the image using the URL with newCellImage, the files are required to be publicly shared. I thought that the reason for your issue might be due to this.
• When appendRow is used in a loop, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

In this modified script, Drive API is used. So, please enable Drive API at Advanced Google services. And, please set your folder ID.

function listFilesInFolder() {
  var folderId = "###"; // Please set the folder ID.

  var sheet = SpreadsheetApp.getActiveSheet();
  var files = Drive.Files.list({ q: `'${folderId}' in parents and trashed=false`, fields: "items(webContentLink,thumbnailLink,title)", maxResults: 1000 }).items;
  var data = [["Name", "URL", "Image"], ...files.map(({ webContentLink, thumbnailLink, title }) => [title, webContentLink, SpreadsheetApp.newCellImage().setSourceUrl(thumbnailLink.replace(/\=s. /, "=s1000")).build()])];
  sheet.getRange(sheet.getLastRow()   1, 1, data.length, data[0].length).setValues(data);
}

• When this script is used, the files are retrieved from the specific folder, and created an array for putting into the active sheet. In this case, the image URL is from thumbnailLink. By this, the image can be put to the cell using newCellImage.

Note:

• In this script, it supposes that the number of files is less than 1000. If your files are more than 1000, please retrieve all files using pageToken. This thread might be useful. Ref

• And also, in this script, from your script, it supposes that all files in the folder are images. When the files except for the image are existing, an error occurs. Please be careful about this.

• When the image is put on the Spreadsheet, there is the maximum image size. Ref But, in this modification, by using thumbnailLink, this limitation can be avoided.

• As an additional information, when your files are put in the shared Drive, please modify as follows.

  • From

      var files = Drive.Files.list({ q: `'${folderId}' in parents and trashed=false`, fields: "items(webContentLink,thumbnailLink,title)", maxResults: 1000 }).items;
    

  • To

      var files = Drive.Files.list({ q: `'${folderId}' in parents and trashed=false`, fields: "items(webContentLink,thumbnailLink,title)", maxResults: 1000, corpora: "allDrives", includeItemsFromAllDrives: true, supportsAllDrives: true }).items;
    

References:

• Files: list
• map()