Geek Answers Handbook

How can I programmatically place a disk image in the Sheets cell?

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

I use Google Picker to upload files to a job-specific folder. I do not know how best to use them from there.

I had success setting the cell formula, for example =IMAGE("hllp://i.imgur.com/yuRheros.png", 4, 387, 422) , but only with images pulled from other places on the Internet.

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

Another way I read, but have not tried it yet, is to write the actual blob into a cell. However, I suspect that I will lose control over the subsequent formatting of the report.

I may need to write the image specification in several ways in several cells:

  • Drive hash
  • its dimensions
  • its alternate location at imgur.com (or similar)
  • his blob

Is there a programmatic way to get the Google redirect URL for the image, which is equivalent to opening the image and manually copying the URL? Can you trust him forever or change over time?

Update : I am wrong about = IMAGE ("hllp: //drive.google.com/uc? Export = view & id = 0B8xy-TdDgbjiFoOldUlLT091NXM", 4, 387, 422) not at work. It is very important that the image be transferred to “anyone with a link”, even if the owner of the table also owns the image.

I am going to record with recording only 1.hash key and 2.dimensions as my solution, but I would be glad to know if anyone has any better idea.

+7
google-spreadsheet google-drive-sdk google-apps-script google-picker
source share
2 answers

I made two lines of script to use the image sharing link in Google Drive.

  • Go to Tools> script.
  • Copy, paste the following code

  • Click "Run for permission"

     function DRIVE_IMAGE(link){ prefix_url = "https://docs.google.com/uc?export=download&"; link.replace("open?", "uc?export=download&"); }

Using script:

  • Copy the sharing link of your image to Google Drive.
  • Go to cell

  • Enter the formula

     =IMAGE(DRIVE_IMAGE("COPIED_LINK"))
+3
source share

Assuming you get the identifier of your image on your disk, you can use the code as shown below to insert the image in the last line of the worksheet (URL is slightly 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 advise you to carefully check the file sharing properties you are trying to show: they should be set to "public" moved to a folder that is also "publicly shared"

+3
source share

More articles:


All Articles