Error retrieving image from URL with SpreadsheetApp.newCellImage() builder

Question

My application is trying to insert images to google drive sheet using google app script.

It works fine... but it hang up intermittently with the response error from google script:

Exception: Error retrieving image from URL or bad URL: https://drive.google.com/uc?id=1uvjg9_ZZg2sI5RYbPMEn6xXJhhnwuFyq&export=download.

The code is :

    var fpm_mon_image = file_from_folder(id_folder_images , image_AR ) ;   // get "image_AR" from folder
    var url_mon_image = fpm_mon_image.getDownloadUrl() ; 
    var image = SpreadsheetApp.newCellImage().setSourceUrl(url_mon_image).setAltTextTitle(titre).toBuilder().build() ;    
     Utilities.sleep(1000);   // for testing ...
     SpreadsheetApp.flush();
     Utilities.sleep(1000); 
     var rangeIma= fpm_sp.getRange(scal_li_SP +1, 2) ; 
     rangeIma.setValue(image).setVerticalAlignment('bottom')  ; // stop here with error above

It works fine 5, 10 times then it hang up 2, 3, 5 times and then works fine again.... (I start loosing my hairs ;-))

I tried :

 var srcfile = DriveApp.getFileById(id_mon_image);
 srcfile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
 var image = fpm_sp.insertImage(srcfile.getBlob(), my_col , my_row);

but the image in not inserted in a cell...

Could you help please ? Many thanks.

By guessing your situation, I proposed a sample script as an answer. Could you please confirm it? If that was not useful for your situation, I apologize. — Tanaike
– Tanaike, Commented Jun 30, 2022 at 0:19

Tanaike · Accepted Answer · 2022-06-30 00:19:09Z

2

Unfortunately, I cannot replicate your situation of Exception: Error retrieving image from URL or bad URL:. So, although I'm not sure about your actual situation, how about the following modification?

Modified script:

Before you use this script, please enable Drive API at Advanced Google services.

var fileId = "###"; // Please set the file ID of your image.

var url = Drive.Files.get(fileId).thumbnailLink.replace(/\=s.+/, "=s512");
var title = "sample title";
var image = SpreadsheetApp.newCellImage().setSourceUrl(url).setAltTextTitle(title).build();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A1").setValue(image);

When this script is run, a thumbnail link is retrieved from the image file using Drive API. And, the image size of the image is changed. And, the image is put to the cell "A1" of the active sheet.
In this case, if the issue of image size occurs, this issue can be removed. And, if the issue of sharing the file occurs, this issue can be also removed. By this, I thought that your issue might be able to be removed.

answered Jun 30, 2022 at 0:19

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marquant Over a year ago

Hi, I tested it and it works well although we are loosing image quality : Original image is 1200 x 676 Pixels and 1556 ko Inserted image is 512 x 288 Pixels and 320 ko Many thanks, Patrick

Marquant Over a year ago

Sorry, I checked the size of the inserted images that are in png format. The original image stored in my drive was in jpg format 1200*676 pixels and only 128KB. (You have le link in the original question) It is therefore strange to crash intermittently with such a small image and a pity to lose quality :-(( So, i will live with it :-)) Thanks. Patrick

Marquant Over a year ago

I changes ...var url = Drive.Files.get(fileId).thumbnailLink.replace(/\=s.+/, "=s512"); ...to... var url = Drive.Files.get(fileId).thumbnailLink.replace(/\=s.+/, "=s1200"); and it works without loosing quality. I apologize for my haste. I will have to think more before commenting ;-)) Thanks again. Patrick

Collectives™ on Stack Overflow

Error retrieving image from URL with SpreadsheetApp.newCellImage() builder

1 Answer 1

Modified script:

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related