7

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.

Community
  • 1
  • 1
Martin Bramwell
  • 2,003
  • 2
  • 19
  • 35
  • I just asked a similar question here: http://webapps.stackexchange.com/questions/86081/insert-image-from-google-drive-into-google-sheets For now I use Picasa to solve the problem, but it's not ideal as the image urls have to be copied one by one. – Jonny Oct 30 '15 at 09:48

3 Answers3

4

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"

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Yes, Thanks Serge, I do have that bit already, but undoubtedly future viewers will benefit from seeing how to do it. Since it is a report, I actually must use =image(url, 4, width, height) to control the dimensions. I calculate the ratio of height to width and then scale to fit the lesser of the two cell dimensions. – Martin Bramwell Nov 07 '14 at 15:08
  • the https://drive.google.com/uc?export=view no longer works for image preview. I just tried this and did not get a thumbnail. – rahulserver Jun 06 '15 at 17:33
  • still working, insert this in a spreadsheet cell : =image("https://drive.google.com/uc?export=view&id=0B3qSFd3iikE3TUFFLTJiOWYzNThhLTNhYzQtNGZlMi1hZjJhLWExNTZhNWY1MzEzYg") – Serge insas Jun 06 '15 at 18:36
  • Hello mister Cooper, your assertive assumption isn't true, this is still working, the formula is like this : `=image("https://drive.google.com/uc?export=view&id=0B3qSFd3iikE3aVdDVGkycFI2bkU") ` for examples look at this sheet ( go to row > 200 for valid images ): https://docs.google.com/spreadsheets/d/1X3EnT6PJN0aochHzBNcLYzyHP78-XT9yOnnpQbQ4If0/edit?usp=sharing – Serge insas Oct 01 '17 at 00:07
  • @Martin Bramwell I cannot find documentation for =image that allows specification of width and height. The following does not work???? let greenButton = mainSheet.insertImage("https://docs.google.com/uc?id=" + fileId, 3, 6); greenButton.setHeight(95) .setWidth(95) .setHorizontalAlignment("center") .setVerticalAlignment("middle") .assignScript('refreshChoices'); – aNewb May 12 '21 at 20:56
  • @aNewb -- 7 years is an eternity in Google's cloud tools, most likely they have changed **everything** in that time. For which reason I refuse to use them anymore. Sorry. Can't help. – Martin Bramwell May 15 '21 at 13:26
3

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"))
Bhacaz
  • 426
  • 5
  • 7
  • You should post the full solution _here_ instead of linking to it. I realize that the site at the other end of the link is a Stack Exchange site, but what if that question or your answer were deleted? Then this wouldn't make much sense on its own. –  Mar 15 '17 at 19:33
  • This looks like the answer I was looking for, but it's so long ago, I no longer need it. Why, now, set it to be the right answer? . . . well Bhacaz :-) – Martin Bramwell Apr 19 '17 at 00:36
  • 1
    The error I get while using this is "cannot call method "replace" of undefined. Any idea? – levitopher Jul 29 '18 at 20:38
  • I retested and I also got the error. So I update my answer to make it work again. – Bhacaz Jul 30 '18 at 14:30
0

We build a lot of sheets with images and use the static link available in the google album archive { https://get.google.com/albumarchive/... } rather than the dynamic link in google photos. the link in the archive normally ends in "-rw" which limits view-ability to some with whom the doc is shared with. Deleting "-rw" from the end of the link seems to help.

Molinare Vfx
  • 1
  • 1