What is the right way to put a Drive image into a Sheets cell programmatically?

Question

I have a WebApp that collects work site data into a Google Sheets spreadsheet and also collects work site photos into a Google Drive folder that I create for each job. Some, but not all, of the photos must be viewable in a cell in Google Sheets, such that the sheet can be printed as part of a job completion report.

I use Google Picker to upload files to the folder specific to the work site job. I am unsure of the best way to use them from there.

I have had success setting a cell formula such as =IMAGE("hllp://i.imgur.com/yuRheros.png", 4, 387, 422), but only with images pulled from elsewhere on the web.

Using the permalink trick like this =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) does not work; I think it won't tolerate the URL redirect that Google does on those links.

The other way I have read about, but not tried yet, is to write the actual blob into the cell. However, I suspect I will lose any control over subsequent formatting of the report.

Perhaps I am going to need to record the image specification in several ways in several cells:

1. its Google Drive hash key
2. its dimensions
3. its alternate location in imgur.com (or similar)
4. its blob

Is there a programmatic way to get Google's redirected final URL for an image, equivalent to opening the image and copying the URL by hand? Could one trust it for ever, or does it change over time?

Update : I am wrong about =IMAGE("hllp://drive.google.com/uc?export=view&id=0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) not working. It is essential that the image be shared to "anyone with the link", even if the owner of the spreadsheet is also the owner of the image.

I am going to go with recording just 1.hash key and 2.dimensions as my solution, but I'd be delighted to know if anyone else has a better idea.

Solution 1

I made a two lines script to use the share link of an image in Google Drive.

1. Go to Tools > Script editor.
2. Copy, paste the following code
3. Save

function DRIVE_IMAGE(link){
  return link.replace("open?", "uc?export=download&");
}

Using the script :

1. Copy the Get shareable link of your image in Google Drive (Maybe you need to set the share preference to Public on the web).
2. Go to a Google sheets cell.

3. Enter the formula

   =IMAGE(DRIVE_IMAGE("COPIED_LINK"))
   

Solution 2

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"