Update text in handlers in the html page from google sheet

[Michail Kotantakis]

Hi team,

I have a google sheet and with an app script i pull the values of one row, then render the values and converting the values to objects

function UpdateTemplate(){

  var data = SpreadsheetApp.getActive().getSheetByName("Maintenance").getDataRange().getDisplayValues();

  return data;

//Logger.log(data);

function renderTemplate(index, data) {

  var index = HtmlService.createTemplateFromFile('index');

  var output = index;

  var params = index.match(/\{\{(.*?)\}\}/g);

  params.forEach(function (param) {

    var propertyName = param.slice(2,-2); //Remove the {{ and the }}

    output = output.replace(param, data[propertyName] || "");

  });

  return output;

  //Logger.log(output);

}

function rowsToObjects(rows) {

  var headers = rows.shift();

  var data = [];

  rows.forEach(function (row) {

    var object  = {};

    row.forEach(function (value, index) {

      object[headers[index]] = value;

    });

    data.push(object);

  });

  return data;

  //Logger.log(data);

}

I also have an index.html page where i have some paragraphs with text in handlers, for example:  Hello {{name}}, here you can find the {{info}} you are looking for. By the {{date}} and {{time}} {{timezone}} you should bring it back

I use the do get function in order create the page :

function doGet(e){

return HtmlService.createTemplateFromFile('index').evaluate();

}

and inside the index.html i have the function to call the code.gs  function from a button click: as following 

function UpdatetheHandlers(){

google.script.run.UpdateTemplate();

}

I can see from the executions that the function Update template runs and completes and when i enable the loggel.log(data); option i can see the values coming from the sheet. But the text is not updated?

Any solutions on this guys? Thanks in advance

Reply to all

Reply to the author

Forward

[Clark Lind]

It looks like you aren't passing into the template the desired variable to replace the brackets. Here is a simple example with a single call to a sheet to retrieve a value, and pass it into the index file to be rendered:

//function to get value from sheet
function getEventColor() {
  return  SpreadsheetApp.getActiveSpreadsheet()
          .getSheetByName('events')
          .getRange("F2")
          .getDisplayValue();
}

function doGet(e) {
//get the required data from the sheet first thing

   var eventColor = getEventColor();
   return createResponse(eventColor); //you can obviously pass anything to the below function
 }    
 

function createResponse(eventColor) {

//store the index file template in a variable so we can add our desired variables (bracket values)
  var template = HtmlService.createTemplateFromFile('index');

//use the format template.bracket and assign the appropriate value
    template.eventColor = eventColor;

//in your case:

//template.name

//template.info

//template.date

//template.time

//template.timezone
 
  return template.evaluate()
      .setTitle('Activity Map') //set your own page title for the rendered html page in the browser
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); //this maximizes permissions for the script. Not sure if it is still used
}

Good luck!

[Michail Kotantakis]

Thanks a lot for your suggestion.
I am going to give it a try tonight and give you some feedback for this

I appreciate your effort

Best Regards

[Michail Kotantakis]

I have tried the way you proposed, i can see th function getting executed without error but the text in the handlers in the test.html file is not replaced. I call the createResponse function with a button named Send email

Even if i refresh the page the values are not updated. Could you please have a look below and advise?

Also is this doable the way i am trying with the a button to call the function, get the latest data from the sheet and replace the text in the handlers, or does it work only on page load? I am interested for the first option to update manually with the click of a button.

On Thursday, 3 November 2022 at 15:18:30 UTC+1 cwl...@gmail.com wrote: