Showing thumbnails in a Google sheet

Question

The code below generates the IMAGE function for the sheet to show thumbnails of all (PDF) files in a chosen folder, obtained with a URL and the file ID:

function scannedMail() {
  var files, file, sheet;
  sheet = SpreadsheetApp.getActive().getSheetByName('ScannedMail');

  files = DriveApp.getFoldersByName("ScannedMail").next().searchFiles('');
  var i = 1;
  while (files.hasNext()) {
    var file = files.next();
    var ID = file.getId();
    sheet.getRange('A' + i).setValue("=IMAGE(\"https://drive.google.com/thumbnail?authuser=0&sz=w320&id=" + ID + "\"\)");
    sheet.getRange('B' + i).setValue(file.getName());
    i=i+1;
  }
}

Yet it does not show the thumbnails. I found out that it shows just the ones where I manually retrieved the ID from getting a "shareable link". Apparently this ensures the right share settings to get the thumbnails of my own files.

1) Is the previous assumption correct, and why do I need to adapt share settings somehow, where I have read other files without any issues?

2) How can I adapt the script to adapt the share settings, or make it work otherwise? The script is meant to operate just within my own Google account, and to keep the files private.

I tried sharing the folder with myself, but that does not make a difference (or sense). Is the script somehow regarded as being another user than myself?

Following suggestions from @Rubén and @Cooper, I have tried using insertImage either based on a URL: sheet.insertImage(file.thumbnailLink, 1, i) or based on a blob: sheet.insertImage(file.getThumbnail(), 1, i)

But the most I could get out of Google was "We're sorry, a server error occurred. Please wait a bit and try again", with the code below:

function ScannedMail() {
  var files, file, , name, blob, sheet;
  sheet = SpreadsheetApp.getActive().getSheetByName('ScannedMail');
  files = DriveApp.getFoldersByName("ScannedMail").next().searchFiles('');
  var i = 1;
  while (files.hasNext()) {
    file = files.next();
    name = file.getName();  //not needed, just for debugging
    blob = file.getThumbnail();
    sheet.insertImage(blob, 1, i);  // it runs up to here...
    i = i + 1;
  }
}

The code execution gets stuck on the first occurrence of insertImage(). So we have 3 approaches (IMAGE function in sheet, insertImage(URL,1,1), and insertImage(blob,1,1)) but all 3 do not make a thumbnail appear, apart from the first method when you make the file public (not a serious option).

I don't see a duplicate question and answer that helps me find out what is wrong with my code, or helps me to somehow get the required thumbnails in the spreadsheet. The kindly proposed solutions did not succeed in that yet.

This question has been incorrectly tagged as a duplicate. The referenced question is totally different. — pinoyyid, Jan 14 '19 at 10:32
You say "1) Is the previous assumption correct?" The assumption is not correct. I suspect that what is actually happening is that Google only generates the thumbnails when the thumbnailUrl is fetched. It's also wrong to assume how the thumbnail URL is constructed. Eg. One of my thumbnails has this URL https://lh3.googleusercontent.com/1_CbSwZ_xnrhojxkk1roKfFJTrn6_YQFBPikTuSRiMG3zdchkR5zoAgAXdSpc4DNqaBqMUbdXaw=s220 — pinoyyid, Jan 14 '19 at 10:36

Cooper · Answer 1 · 2019-01-14T03:03:43.547

1

Try something like this:

function imgArray() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('ImageArray');
  if(!sh){
    sh=ss.insertSheet('ImageArray');
  }
  var imgA=[];
  var folder=DriveApp.getFolderById('folderid');
  var files=folder.getFiles();
  while(files.hasNext()){
    var file=files.next();
    var filename=file.getName();
    imgA.push(file.getBlob());

  }
  for(var r=0;r<imgA.length;r++){
    sh.insertImage(imgA[r],1,r+1);
  }
}

This was adapted from an answer from @Tanaike.

I guess this is what you were looking for:

function ScannedMail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('ScannedMail');
  var files = DriveApp.getFoldersByName("ScannedMail").next().searchFiles('');
  var i = 1;
  while (files.hasNext()) {
    var file = files.next();
    var blob = file.getBlob();
    sheet.insertImage(blob, 1, i);  // it runs up to here...
    i = i + 1;
  }
}

edited Jan 14 '19 at 03:03

answered Jan 13 '19 at 23:32

Cooper

59,616
6
23
54

Your script uses `insertImage` with a blob. I don't see anything appear in the sheet (it finds the image files, but the regex seems to discard it maybe). In any case, I used your idea to use `insertImage` with a blob (it was actually what I tried first). I iterate through my PDF files putting thumbnails in the sheet from within that loop (no array needed): `sheet.insertImage(files.next().getThumbnail(), 1, i);` Unfortunately it gives a rather meaningless error: "We're sorry, a server error occurred. Please wait a bit and try again". – user3288616 Jan 14 '19 at 00:58
Yes. I was assuming that you would rearrange it for your own needs. This is a script that I'm using in my own work. I have some other methods commented out. I've edited it now so it will use all of the files in the folder so hopefully in your case they're all images. But again tweak it to you needs. – Cooper Jan 14 '19 at 02:28
Thanks @Cooper. Unfortunately that code never gets past the `insertImage` line, and only gives me "We're sorry, a server error occurred. Please wait a bit and try again" (see updated question). – user3288616 Jan 14 '19 at 10:41
First of all I checked the sharing of my image files and spreadsheet and neither are public. I also have seen that error but after I edited the files down to 100 px square the error stopped and the images appeared in the spreadsheet. Current only I can view them but I would guess that if you share everything to the same audience it might work. – Cooper Jan 14 '19 at 17:43
I just shared the images and the spreadsheet with another account and it works okay. Plus anyone in my domain could view as well. However, there is no one else in my domain so I can't check to be sure. – Cooper Jan 14 '19 at 18:03
I would remove the getThumbnail() and resize the images myself and my guess is that it will work. It does for me. – Cooper Jan 14 '19 at 18:33
Both `sheet.insertImage(files.next().getBlob(), 1, i)` and `sheet.insertImage(files.next().getAs('application/pdf'), 1, i)` give me the infamous: `We're sorry, a server error occurred. Please wait a bit and try again.` I think I am going to have to wait indeed, thanks for all your work! – user3288616 Jan 14 '19 at 22:04
Oh, they're pdf files. That's a new twist. I haven't seen that in your question before. – Cooper Jan 14 '19 at 23:20

Rubén · Answer 2 · 2019-01-13T21:27:39.017

0

Google Sheets IMAGE built-in function only is able to retrieve images that are publicly available, so, yes you should have to adapt the sharing settings to make the images viewable by anyone.

In order to keep the files private you should not use IMAGE built-in function, you could use one of the methods of Class Sheet to insert images like insertImage(blobSource,column,row). See answer to Adding Image to Sheet from Drive for an example.

NOTES:

As custom functions run anonymously they can't be used them either.
According to Mogsdad answer to InsertImage(url,x,y) doesn't work with Google Drive insertImage(url,row,column) can't be used to insert images from Google Drive

answered Jan 13 '19 at 18:17

Rubén

34,714
9
70
166

Thank you for your answer. Making my private files public to put them in a private spreadsheet is not an option. I tried insertImage before, but (wrongly) with a blob rather than a URL: `sheet.insertImage(file.getThumbnail(), 1, 1)` but that did not work. You rightly point out that it needs to be a URL for the image, but where can I get a URL for a thumbnail of a PDF in my drive? According to this issue in Google's issuetracker [https://issuetracker.google.com/issues/36760259] it is not even possible, which is very disappointing. – user3288616 Jan 13 '19 at 20:28
I thought this would finally work: `sheet.insertImage(file.thumbnailLink, 1, 1)` but no such luck. It is not even clear whether it's either much more complicated or even impossible. – user3288616 Jan 13 '19 at 21:04
@user3288616 I updated my answer. – Rubén Jan 13 '19 at 21:31

Showing thumbnails in a Google sheet

2 Answers2