Adding Image to Sheet from Drive

Question

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);

}

Answer 1

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