2

I would like to create a signature png file save in G Drive and have it paste into a cell with a menu item. I have set the image as public and an trying to use the shared URL, but this doesn't work adding =IMAGE or doing this programmatically. I have tried various pieces of code from the interwebs and have not been successful to date. Last attempt at code listed.

function mysig() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rng = sheet.getRange(34,6);
  var url = "https://drive.google.com/file/d/id/view?usp=sharing"
  var fetch_img = UrlFetchApp.fetch(url);
  var blob = fetch_img.getBlob();
  Logger.log(fetch_img.getBlob())
  sheet.insertImage(blob, 6, 34);

}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Robert Miller
  • 23
  • 1
  • 5
  • 2
    Take a look at this [previous SO question](https://stackoverflow.com/questions/26801041/what-is-the-right-way-to-put-a-drive-image-into-a-sheets-cell-programmatically), it might help you. – Pierre-Marie Richard Jul 20 '17 at 17:00
  • I had been through that as well and it doesn't work either. Just using the =IMAGE function in the spreadsheet with the URL to the image in Drive doesn't work either. – Robert Miller Jul 20 '17 at 18:03

1 Answers1

5

Use Drive App to generate the blob of the google drive image. Refer the below code.

function insertImage() {
  var openSpreadSheet = SpreadsheetApp.openById("spreadSheetID").getSheetByName("Sheet1");
  var getImageBlob = DriveApp.getFileById("GooglDriveImageID").getBlob();//0B5JsAY8jN1CoNjlZR2tUUHpISFE
  openSpreadSheet.insertImage(getImageBlob,2,2,100,200); //insertImage(blob, column, row, offsetX, offsetY)
}

Edit 2: Using image formula - To match the cell size

function insertImage() {
  var openSpreadSheet = SpreadsheetApp.openById("spreadSheetID").getSheetByName("Sheet1");
  var url = "https://docs.google.com/uc?export=download&id=0B5JsAY8jN1CoNjlZR2tUUHpISFE";
  openSpreadSheet.getRange(5, 3).setFormula('=image("'+url+'",2)')
}

  • =image("URL") – image retains aspect ratio while increasing / decreasing the cell size

  • =image("URL",2) – image stretches to fit all edges of the cell

  • =image("URL",3) – image retains its original size

  • =image("URL”,4,50,50) – set the size of the image by replacing 50,50 with desired dimensions

Ritesh Nair
  • 3,327
  • 1
  • 17
  • 24
  • Awesomeness! Is there a way to constrain the size of the image by the cell size like you can with the =IMAGE function? I see an offsetX and offsetY – Robert Miller Jul 20 '17 at 19:58
  • Welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted – Ritesh Nair Jul 20 '17 at 20:19
  • That just offsets it 100 x 200 from the original cell reference, it doesn't resize the image to match the cell size. Thanks for the info so far. – Robert Miller Jul 20 '17 at 20:22
  • That doesn't work either, programmatically or hard writing it as a formula into the cell. I am not sure this is even supported as all posts for this on the interwebz are extremely old. – Robert Miller Jul 21 '17 at 13:49
  • What are the security settings for the image in Google Drive? I have set it to public and it still doesn't work. The blob insert works fine, but it doesn't size it. – Robert Miller Jul 21 '17 at 15:11
  • 1
    Got it, my URL was bad. I think I have this sorted out now so thanks for all your patience, – Robert Miller Jul 21 '17 at 15:16
  • I changed the permissions on the signature file from Public to Internal org only and the functionality has stopped working. Even when I changed it back to Public. Does it take time for permissions propagate? Was working flawlessly until I made this change. – Robert Miller Aug 08 '17 at 16:54
  • @Ritz The function is not working with picture shared internally only for user Gsuite domain (not public access). Have you a solution? – Mohamed H Feb 19 '20 at 22:07