What is the right way to put a Drive image into a Sheets cell programmatically?
Assuming you get the ID of your image in your drive, you can use a code like below to insert an image in the last row of a sheet (the url is a bit different than usual):
...
var img = DriveApp.getFileById('image ID');// or any other way to get the image object
var imageInsert = sheet.getRange(lastRow+1, 5).setFormula('=image("https://drive.google.com/uc?export=view&id='+img.getId()+'")');// in this example the image is in column E
sheet.setRowHeight(lastRow+1, 80);// define a row height to determine the size of the image in the cell
...
I would suggest that you carefully check the sharing properties of the files you are trying to show : they must be set to "public" of moved in a folder that is also "publicly shared"
I made a two lines script to use the share link of an image in Google Drive.
- Go to Tools > Script editor.
- Copy, paste the following code
- Save
function DRIVE_IMAGE(link){
return link.replace("open?", "uc?export=download&");
}
Using the script :
- Copy the Get shareable link of your image in Google Drive (Maybe you need to set the share preference to Public on the web).
- Go to a Google sheets cell.
Enter the formula
=IMAGE(DRIVE_IMAGE("COPIED_LINK"))