CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apispreadsheet
Obaid
Obaid

Reputation: 79

Pull Data from Google Sheets and display in HTML H3 tag

I'm trying to pull data from Google Sheets and display in H3 tags which has specific IDs. Here's the link to my Google Sheet. I only want to pull Prices from that sheet and display within the H3 tags. How can I achieve this?

Code.gs

function doGet() {
var temp = HtmlService.createTemplateFromFile('index');
temp.list = getValues();
return temp.evaluate();
}

function getValues() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName('Sheet1');
var lastRow = ws.getLastRow();
var numRow = lastRow - 1;
return ws.getRange(2,1,numRow,2).getValues();
}

HTML

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>50-74 gallons: </h1><h3 id="firstPrice"></h3>
    <h1>74-99 gallons: </h1><h3 id="secondPrice"></h3>
    <h1>100-124 gallons: </h1><h3 id="thirdPrice"></h3>
  </body>
</html>

Upvotes: 1

Views: 342

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Since you already have the sheet values in a template variable (list), iterate through that variable, taking into account that it's a 2D array and that each inner array has two elements, corresponding to Gallons and Price:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <? for(var i=0; i<list.length; i++){ ?>
      <h1><?= list[i][0] ?></h1>
      <h3 id=<?= `price_${i+1}` ?>><?= list[i][1] ?></h3>
    <? } ?>
  </body>
</html>

If you want each h3 to have an id representing the index (as you wrote firstPrice), you could add the loop index to each id, for example as shown in the snippet above:

id=<?= `price_${i+1}` ?>

Important note:

Please note that this will only work for Apps Script web apps. The syntax <?= ?> is specifically designed for Apps Script, and will not work if you try it in another environment. See scriptlets.

Edit:

If you don't want to use a loop, but to write the HTML elements manually, you would do something like this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1><?= list[0][0] ?></h1><h3 id="firstPrice"><?= list[0][1] ?></h3>
    <h1><?= list[1][0] ?></h1><h3 id="secondPrice"><?= list[1][1] ?></h3>
    <h1><?= list[2][0] ?></h1><h3 id="thirdPrice"><?= list[2][1] ?></h3>
  </body>
</html>

Upvotes: 1

Related Questions