Insert image into Spresheet cell from disk using Google Apps Script

Question

I would like to be able to add an image file to a spreadsheet from Google Drive. I see that there is a built-in image function available = image, but this requires a URL and that your image files are publicly available on the Internet, I work with digital resources and cannot publish them publicly.

I have the following code, this works, but does not add to the required cell, is this 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) } 

Answer 1

Try using the Insert Image to Spreadsheet guide from the Script application:

 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 required values. The part in which you indicate which cell you are inserting is in:

 sheet.insertImage(blob, 1, 1); 

Answer 2

There is an ImageKit add-in for Google spreadsheets to insert multiple images from differences sources, including Google Drive, check this> https://chrome.google.com/webstore/detail/imagekit/cnhkaohfhpcdeomadgjonnahkkfoojoc Here you will find a screenshot of the tool interface.

Answer 3

I do not think this is currently possible, see here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3303

Answer 4

But the answer is wrong. This method inserts an image on top of a spreadsheet, not a cell.

Answer 5

This is not possible if the disk image file is not publicly available on your Google drive, saying that there is a trick to overcome i.

First, make sure the image file is temporarily open on your disk. You can also do this through the application script by changing the file permissions to DriveApp.Access.ANYONE , DriveApp.Permission.EDIT . Then the disk file looks like a file as if it were from the Internet. Then you can add a drop.

After that, you can decide two things. Change the permissions back or delete the image file from your disk (if you want to use it only built-in to your blob-object (also, if you want, through the application script code)

good luck