CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-drive-api
Graham Bourne
Graham Bourne

Reputation: 71

Insert Image into Spreadsheet Cell from Drive using Google Apps Script

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

Upvotes: 7

Views: 33668

Answers (5)

Aaron Record
Aaron Record

Reputation: 340

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

Upvotes: 2

Ralph van der Horst
Ralph van der Horst

Reputation: 31

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

Upvotes: 3

Albert
Albert

Reputation: 134

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. imagekitAddon

Upvotes: 1

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17613

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

Upvotes: 10

utphx
utphx

Reputation: 1285

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

Upvotes: 0

Related Questions