Can I format html using GAS inside scriplets-CodePudding

i am doing a basic web app based on a google spreadsheet

in the spreadsheets dates are formatted dd/mm/yyyy

in the web app view they look like this

Fri Feb 04 2022 00:00:00 GMT 0000 (Greenwich Mean Time)

how can I get control over the html display and make it dd/mm/yyyy?

Tom

CodePudding user response：

There are 2 ways I know to achieve this. 1. use getDisplayValue() 2. use Utilities.formatDate

function date(){
  const sheet = SpreadsheetApp.getActive();
  const v1 = sheet.getRange('B2').getValue();
  const v2 = sheet.getRange('B2').getDisplayValue();
  const v3 = Utilities.formatDate(new Date(v1),'GMT -6','dd/MM/YYYY')
  console.log(v1,v2,v3)
}

// output
Mon Dec 20 2021 01:00:00 GMT-0500 (Eastern Standard Time)
'20/12/2021'
'20/12/2021'

CodePudding user response：

You need to format it in your script first and then you can display it on your HTML. Here's a generic function you can use

function formatDate() {
  const monthNames = ["January", "February", "March", "April", "May", "June",
        "July", "August", "September", "October", "November", "December"];
    const date = new Date();
    const month = monthNames[date.getMonth()];
    const day = String(date.getDate()).padStart(2, '0');
    const year = date.getFullYear();
    const output = `${day} ${month},${year}`
    return output
}

If you need a specific format, you can modify it. Even you can add a parameter to enter any date you want.

CodePudding user response：

You can also use scriptlets to run normal JavaScript within your HTML output. Data can be loaded into the HTML document from a Google Sheet in a number of ways; see the linked Google documentation for more info.

Once you have data accessible from the HTML output--in your case, date-time data--you can run normal JavaScript methods on that data in printing scriptlets. Use toLocaleDateString() on Date objects, for example, or another Date method to get the formatting you want.

For example, here's one way of doing things, adapted from the Google documentation.

Your Code.gs file might look like this:

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

function getDate() {
  return SpreadsheetApp
      .openById('SPREADSHEET_ID')
      .getActiveSheet()
      //some range with Date data
      .getRange('A1')
      .getValue();
}

And you could include scriptlets in your Index.html that look like this:

<!DOCTYPE html>
<html>

  <head>
    <base target="_top">
  </head>

  <body>
    <? var data = getDate(); ?>
    <div>
      <?= data.toLocaleDateString() ?>
    </div>
  </body>

</html>