Insert Image into Spreadsheet Cell from Drive using Google Apps Script

Question

I would like to be able to add an image file into my spreadsheet from Google Drive. I see there is a built-in image function available =image, but this requires a URL and that image files should be shared publicly on the internet. However, I am working with digital assets and can not share them publicly.

I have the following code, this works but does not add to the required cell. Is this at all possible?

function insertImageFromDrive(){
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var fileId = '0B5UAkV0avfiKNGYtd1VzUzlsTVk';
  var img = DriveApp.getFileById(fileId).getBlob();
 
  
  sheet.insertImage(img, 4, 3)
}

Universe

Answer 1

Try using the guide Insert image in a spreadsheet from App Script:

function insertImageOnSpreadsheet() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var response = UrlFetchApp.fetch(
      'https://developers.google.com/adwords/scripts/images/reports.png');
  var binaryData = response.getContent();

  // Insert the image in cell A1.
  var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
  sheet.insertImage(blob, 1, 1);
}

Just replace the necessary values. The part where you indicate which cell you insert the image is in:

sheet.insertImage(blob, 1, 1);

Hasidism

Answer 2

It is not possible if the drive image file is not public on your google drive, Having said that there is a trick to overcome i.

First make sure the image file is temporary public on your drive. you can do that also via app script by changing the file permissions to DriveApp.Access.ANYONE, DriveApp.Permission.EDIT. Then the drive file is like a file as if it was from the internet. You can then add the blob.

After that you can decide two things. Change the permissions back or remove the image file from your drive (if you only want to use it embedded in your blob (also via app script code if you want)

good luck

Ronel

Answer 3

Here's another alternative:

var fileId = "..."; // your image's google drive id
sheet.insertImage("https://docs.google.com/uc?id=" + fileId, column, row);

Just make sure your file has link sharing turned on (anyone with the link can view) otherwise this method won't work.

Documentation

Ambrosial

Answer 4

There is a google spreadsheet add-on ImageKit to help insert multiple images from difference sources including Google Drive, check it out > https://chrome.google.com/webstore/detail/imagekit/cnhkaohfhpcdeomadgjonnahkkfoojoc Here you find a screenshot of tool's interface.

Cletacleti

Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline. – Sickly

@Sickly Thanks for remindingme, it's a link of that add-on in chrome store, I added a screenshot to give more info on tool. – Cletacleti

Answer 5

I don't think it is currently possible, see here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3303

Scrip

The issue status if Fixed. – Cia