Gists

This is a workaround for inserting the non-public image of Google Drive using IMAGE function in a cell on Google Spreadsheet using Google Apps Script.

When an image on Google Drive is inserted to a cell of Spreadsheet using =IMAGE(URL) function, the image of URL is required to be publicly shared. But, there is the case that the image cannot be publicly shared. This workaround might be able to be used for this situation.

## Usage

This workaround uses Google Apps Script. So, please create Google Apps Script project. In this case, the container-bound script can be also used. In this sample, I used the container-bound script of Spreadsheet.

### 2. Sample script.

function doGet(e) {
// Please set the object. In this sample, "image1" is an identification of the image. This value is used instead of the file ID.
const obj = { image1: "###fileId###" };

const { key } = e.parameter;
const res = obj[key]
: "";
return ContentService.createTextOutput(<res>\${res}</res>).setMimeType(
ContentService.MimeType.XML
);
}

• When you want to change the image size, please modify =s500 to others. It seems that 500 is 500 pixels.

### 3. Deploy Web Apps.

The detailed information can be seen at the official document.

1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
2. Please click “Select type” -> “Web App”.
3. Please input the information about the Web App in the fields under “Deployment configuration”.
4. Please select “Me” for “Execute as”.
• This is the importance of this workaround.
5. Please select “Anyone” for “Who has access”.
• In your situation, I thought that this setting might be suitable.
7. Copy the URL of the Web App. It’s like https://script.google.com/macros/s/###/exec.

### 4. Testing.

In order to test this workaround, please put a following formula to a cell.

=IMAGE(IMPORTXML("https://script.google.com/macros/s/###/exec"&"?key=image1","/res"))

• Please replace https://script.google.com/macros/s/###/exec with your Web Apps URL.
• When this formula is run, you can see the situation like the top of demonstration.
• Sample image is from here.