0

I have a series of images that need to be private and only viewable within our Google Domain. If the image is shared publicly (Anyone on the Internet with this link can view), I can view the image in a Google Sheet using the =image() function, but with the image being private I cannot use the =image() function, which is an expected feature.

Google - Image Function

I am aware that the use of Google Drive as a "web host" has been depreciated, and with this the ability to link to a private document also seems to have been removed.

Deprecating web hosting support in Google Drive

I can manually go via the Insert | Image route and insert a private image into a cell. This allows the image that is private within our domain to be visible in a sheet. I have not been able to find any way to replicate this functionality in a app script. Manually undertaken this work for what I need would be to labour intensive.

Google - Add an image to a spreadsheet

I am also aware that the insertimage() function allows the import of a private file into a sheet, but this function does not import the image into a cell but over a cell and that the functionality to do this doesn’t currently exist.

Inserting Images into a Sheet as BlobSource using Google Apps Script

Google Issue Tracker - Insert Image into Cells

As far as I’ve been able to see there is no specific way of using a private file in Google Docs, on a Google Sheet either via an apps script or via built in functions.

If I am able to confirm that there is no possible way of doing this I can then investigate other options, but I have been unable to find a definitive YES/NO statement that explains that the use of private images in a Google sheet can be seen.

A.Steer
  • 335
  • 3
  • 14
  • Your research is thorough. I can't claim to be definitive, but I've spent a lot of time trying to find a workaround to do the same thing, and was unable to. I suspect the answer is NO until Google updates Apps Script or the Sheets API to allow insertion of in-cell images. – Aaron Dunigan AtLee Jan 11 '22 at 16:41
  • @A.Steer, I found a workaround [here](https://stackoverflow.com/a/61735314/17390145). I made a test and it works for me. I'm might be super late, but I wanted to add the answer. – Giselle Valladares Aug 18 '22 at 00:42

2 Answers2

1

I agree with @AaronDuniganAtLee. You already have done sufficient research to confirm this.

Given that there are also mentions of the feature request Google Issue Tracker - Insert Image into Cells on several Stackoverflow posts like Insert Image into Spresheet Cell from Drive using Google Apps Script & Google Apps Script - Insert Image into Spreadsheet cell using Google Drive ID about this matter & given this request is active in a P2 priority on Google Issue Tracker site, then the answer is No.

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • cheers for that, I thought it might be the case, but I was going round in a circle with vague mentions of possible fixes, but most of them didn’t explicitly mention the use of public/private files. – A.Steer Jan 12 '22 at 08:17
0

I found a solution.

var fileId = "***";
    
var fileURL = `https://lh3.google.com/u/0/d/${fileId}=s2048`;

var cell = sheet.getRange("A1");

cell.setFormula(`=IMAGE("${fileURL}")`);
Nutmeg
  • 1
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32458616) – ehambright Aug 17 '22 at 16:21
  • If you made a test, and it worked for you. You can leave a proof to backup your answer or any documentation that can back off your answer. – Giselle Valladares Aug 18 '22 at 00:46