Search code examples
htmlimagegoogle-sheetsgoogle-apps-scriptweb-applications

Is there a way to display an image in an HTML table directly from a Google Sheets cell?

I am having some difficulty displaying an image in an HTML table in a WebApp populated from Google Sheets data. Yesterday, I created a post that was tagged as a duplicate to this post, but I believe a different solution is being requested.

My Google Sheets data looks like this:

Spreadsheet

In the cells webapp!B1:B I have images based on the forecast for the day. The weather forecast is pulled from Visual Crossing data. The formula logic is something like:

"if the forecast for today is "sunny", choose the picture in the cell Icons!A1, else, if the forecast for today is "partly cloud", choose the picture in the cell Icons!A2, etc."

The pictures to be displayed are "images in the cells" in Icons!A1:A.

My codes for the Webapp are below:

Code.gs

function doGet() {
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function getSheetData()  { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('webapp'); 
  var dataRange = dataSheet.getDataRange();
  var dataValues = dataRange.getValues();  
  return dataValues;
}

HTML

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link
      rel="stylesheet"
      href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
      />
      <style>
      .container {
        padding: 35px 7px 0px 7px;
         }
         
      .all-tables {
        font-size:16px;
        background-color: #EFEFEF;
        border: 1px solid #EFEFEF;
        border-radius: 5px;
        width: 310px;
      }
      
      th, td {
        text-align: left;
        padding-left: 10px;
        padding-top: 5px;
        padding-bottom: 5px;
      }
      </style>
  </head>
  <body class="container">
  <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Poppins">
  <style>
  body {
    font-family: "Poppins", serif;
  }
</style>
  </head>
  <body>
    <table class="all-tables">
    <?var tableData = getSheetData();?>
    <?for(var i = 0; i < tableData.length; i++) { ?>
      <?if(i == 0) { ?>
        <tr>
        <?for(var j = 0; j < tableData[i].length; j++) { ?>
        <th><?= tableData[i][j] ?></th>
        <? } ?>
        </tr>
      <? } else { ?>
        <tr>
        <?for(var j = 0; j < tableData[i].length; j++) { ?>
        <td><?= tableData[i][j] ?></td>
        <? } ?>
        </tr>
      <? } ?>
    <? } ?>
    </table>
  </body>

The issue with the WebApp is that the images are not displayed. Instead the image is replaced with "CellImage". See the screenshot below:

WebApp

I have tried replacing getValues() with getDisplayValues(). I have also tried changing the cell formatting in the cells webapp!B1:B. In addition, I tried hosting the images on my Wordpress site and using IMAGE() formula to get the image into the spreadsheet. I also tried replacing the image with just the URL of the image. This last change replaces "CellImage" with the URL but I still don't get the image in the WebApp.

Is this something that is possible? The only other alternative I can think of is to try to use the solution from this aforementioned post and put the logic in the script itself that "if the forecast is sunny, choose the image at this URL, etc." but this approach seems much more convoluted to me. Also, I am more versed in writing logic in a formula than in script 😀.

Any guidance or suggestions would be greatly appreciated and I apologize for the lengthy post.

Solution

  • Images when added directly creates a unique publicly accessible url(security issue?) in Google sheets. These urls are not accessible directly from apps script currently. The only way currently is to use =IMAGE("url") to get the images in sheet( which you stated you tried). If you do that, the external urls can be retrieved in Apps script using Range.getFormula().

    Nevertheless, If you use an XLOOKUP to get the image, it is still not possible to get the url from the formula result cell without recreating the formula logic inside apps script.

    However, one another way is to export the spreadsheet as xlsx and get the image data url from there, which is a complicated process, but Tanaike's DocsServiceApp library can be used to achieve this. See sample answer. Once you have the data url, it can be used directly in the anchor tag of html as data url