Using Class CellImageBuilder to import Image into Google Sheets cell

Question

On the 19th January 2022, the CellImageBuilder class was added to the Google Sheets Spreadsheet service.

This class now allows you to import an image into a cell in Google Sheets, previously you could only add an image above the cell.

Ive been trying to use this new class to take a URL link from a Google sheet, and then create an image in the cell next to the URL. This works perfectly fine if I can hard code the URL into the script (example below)

**function insertImageIntoCell()**
    {
    var sheet = SpreadsheetApp.getActiveSheet();  
    
    var url='Google_Docs_Image_URL'
    
    let image = SpreadsheetApp.newCellImage().setSourceUrl(url).setAltTextDescription('TestImage').toBuilder().build();
    
SpreadsheetApp.getActive().getActiveSheet().getRange('C2').setValue(image);
    }

The problem I am having is that once I create an array to iterate through the column the below script creates a valid array and posts it into the correct column and rows, but when it posts it back into the spreadsheet it only returns the URL and does not convert it into an image in a cell

**function insertImageIntoCell()**
{
var sheet = SpreadsheetApp.getActiveSheet();  
var myStringArray = sheet.getRange('B2:B10');
var myStringArray = sheet.getRange('B2:B10').getValues();

//Logger.log(myStringArray)
  
  let image = SpreadsheetApp.newCellImage().setSourceUrl(myStringArray).setAltTextDescription('test').toBuilder().build();

  SpreadsheetApp.getActive().getActiveSheet().getRange('C2:C10').setValues(myStringArray);
}

Im using the followign code to create the initial table of data, this pull the file name and DownloadURL from a Google Drive location and then saves this into a sheet

/* modified from @hubgit and http://stackoverflow.com/questions/30328636/google-apps-script-count-files-in-folder 
for this stackexchange question http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets by @twoodwar
*/
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("Google_Drive_Folder");
    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);
            
    };
};

I am looking for the script to refresh the file information from Google Drive into sheets, then to save the image into a cell, it now appears that this functionality exists, but Im not able to get it to take an array of URL's

Answer 1

Update:

• This issue is filed. Add a star to this issue for Google developers to prioritize fixing this.

Issue:

• setValues() is NOT working with CellImage, while setValue() does.

---

If/when it starts working, You need to convert each value to cellImage using map :

function insertImagesIntoCell() {
  const sheet = SpreadsheetApp.getActiveSheet(),
    range = sheet.getRange('B2:B10');
  range.setValues(
    range.getValues().map(url => [
      SpreadsheetApp.newCellImage()
        .setSourceUrl(url[0])
        .build(),
    ])
  );
}

Answer 2

Suggestion

Perhaps you can try this sample implementation below.

Sample Tweaked Script

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("DRIVE_FOLDER_ID");
  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] == "URL")return row += 1;
      let image = SpreadsheetApp.newCellImage().setSourceUrl(url[1]).setAltTextDescription('TestImage').toBuilder().build();
      SpreadsheetApp.getActive().getActiveSheet().getRange('C'+row).setValue(image);
      row += 1;
  });
}

Sample Drive Folder with sample images

Sample Result:

• After running the function listFilesInFolder:

Answer 3

For anyone struggling with importing images from Google Drive you should know that you have to set the "Sharing" setting on every individual file for CellImageBuilder to work properly.

Like this:

const imageFileUrl = imageFolder.getFilesByName(filename).next()
      .setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW)
      .getDownloadUrl();
const cellImage = SpreadsheetApp.newCellImage().setSourceUrl(imageFileUrl).build();

Additionally, there appears to be a rate limit on the drive download URLs, causing the '.build()' function to fail randomly on a valid URL. Retries might be necessary.

Also, the .toBuilder() call on CellImageBuilder is completely redundant.

Answer 4

I have looked across multiple forums trying to work out how to add a drive image to a cell and finally i have it working!Thanks to viktor Vix!

You have to set the permission on the file before or with the .setSharing() as below and you need the .getDownloadUrl() it that easy! everyone on every forum I know said it cant be done! it can.

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, 
DriveApp.Permission.VIEW);
var fileDownloadUrl = file.getDownloadUrl();
var cellFormula = "=IMAGE(\"" + fileDownloadUrl + "\")"; 

sheet.appendRow([cellFormula]);