Display image in HTML page served by Apps Script

Question

Good afternoon all;

I have a google spreadsheet which is populated by a Qualtrics form, where the user can upload a photo.

Using Apps Script, I have created a CRUD database that is accessed via a google sites page (html form). All is working great, but I'd like to display the photo that the user uploaded in their record; the URL to the image is saved in a column on the spreadhsheet.

The thing is, I'm not a very worthy 'coder' and I can't figure out how to make this happen.

I'm accessing my data like this: Note that I have removed a good portion of the code to save space.

Code.gs

function getFormValues(formObject) {

  if (formObject.responseID && checkID(formObject.responseID)) {
    var values = [[formObject.responseID.toString(),
    formObject.permitNumber,
    formObject.dateOfIssue,
    formObject.photoLink, <<<this is where the URL would be pulled in. Column Q in the spreadsheet.`

DataTable.html

function populateForm(records){
    document.getElementById('responseID').value = records[0][0];
    document.getElementById('permitNumber').value = records[0][1];
    document.getElementById('photoLink').value = records[0][16];

...I just don't know what to do after this point...

Answer 1

I did some tests, and I have an option for you to use Google Apps Script and the formula IMAGE.

Since I didn't have much information on your setup, like where the files are uploaded, which is the format of the URL, etc. I made a test environment using Google Forms, the files uploaded to Google Drive, and Apps Script.

With the formula:

=IMAGE("URL", [mode], [height], [width])

For the test, I use mode 4, which allows you to specify the size of the image. You can use either of the modes:

• 1 resizes the image to fit inside the cell, maintaining the aspect ratio.
• 2 stretches or compresses the image to fit inside the cell, ignoring the aspect ratio.
• 3 leaves the image at its original size, which may cause cropping.
• 4 allows the specification of a custom size.

The height and width needs to be in pixels.

When you use a Google Form to upload an image, you get an URL with the following format:

https://drive.google.com/open?id=[ID_OF_THE_FILE] 

There is a trick to display images from Google Drive into Google Sheets using the image formula. You need the URL with the format: (I got this idea from this post)

https://lh3.googleusercontent.com/d/[ID_OF_THE_FILE] 

So the script that I created modifies the URL in the column of the image from:

https://drive.google.com/open?id=[ID_OF_THE_FILE]  

To:

=IMAGE("https://lh3.googleusercontent.com/d/[ID_OF_THE_FILE]", 4, 60, 60)

And place it in the cell in the column. However, you can place it in the same column as the current URL too.

Here is the sample code:

function testImage() {
  // Identifies the sheet link to the Google Sheet and the tab you will be working with
  // you can also use const sheet = SpreadsheetApp.getActiveSpreadsheet()
  //                                              .getSheetId("[ID_OF_THE_SHEET]"); 
  // if the Script is not bound to the sheet. 
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

  // Gets the range and the values where the URLs of the images are located. 
  // in my example there are in column B
  let range_1 = sheet.getRange("B2:B").getValues().flat();

  // Filters the rows without values or empty
  range_1 = range_1.filter((element) => {return (element !== '')});

  // Start of the loop to run on each row with URLs
  for (let i=0; i< range_1.length ; i++){
    let row = range_1[i];

    // For this sample, I replace the URL with the word "Done" to prevent duplicates later on in the Script
    // This can be skipped or replaced place, in a different location. 
    if (row != "Done"){

      // Replace the URL from https://drive.google.com/open?id=[ID_OF_THE_FILE] 
      // to =IMAGE("https://lh3.googleusercontent.com/d/[ID_OF_THE_FILE]
      let temp_imageURL = row.toString().replace('https://drive.google.com/open?id=', 
      '=IMAGE("https://lh3.googleusercontent.com/d/');

      // However, the process is not complete yet, we need to complete the formula
      // so we added the last part ",4,60,60) to the URL 
      // making it "https://lh3.googleusercontent.com/d/[ID_OF_THE_FILE]",4,60,60)
      let imageURL = temp_imageURL+'",4,60,60)';

      // place the complete formula in the current iteration, row 3 (which is row C)     
      sheet.getRange(i+2,3).setValue(imageURL);

      // replace the current URL in Row 2 (which is row B) with the word "Done"
      sheet.getRange(i+2,2).setValue("Done");
    }
  }
} 

It looks like:

Reference:

• IMAGE
• Replace