tanaikech/submit.md

## submit.md

      
    Raw
  

              submit.md
            
          
    Workaround for Inserting Non-public image of Google Drive using IMAGE Function in a Cell on Google Spreadsheet using Google Apps Script


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.
1. Create Google Spreadsheet.

Please create a new Google Spreadsheet. And, please open the script editor.
2. Sample script.

This sample script uses Drive API. So please enable Drive API at Advanced Google services. Ref
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]
    ? Drive.Files.get(obj[key]).thumbnailLink.replace("=s220", "=s500")
    : "";
  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.

On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
Please click "Select type" -> "Web App".
Please input the information about the Web App in the fields under "Deployment configuration".
Please select "Me" for "Execute as".

This is the importance of this workaround.


Please select "Anyone" for "Who has access".

In your situation, I thought that this setting might be suitable.


Please click "Deploy" button.
Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".


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.


References


Web Apps
Taking advantage of Web Apps with Google Apps Script
IMPORTXML