Passing innerHTML value of ID as the ("string") reference for getSheetByName()

Question

I making a schedule app with pages for each day of the week. There are pages for Mon-Fri. Each page pulls info from their corresponding sheet also labeled Mon-Fri.

So page "Monday" and it's functions should pull from the "Monday" sheet/tab... easy enough.

The simple solution...

Just use getSheetByName("Monday") on the Monday page, "Tuesday" for Tuesday, etc.

Yes this works, BUT I have multiple functions to run for each page. Meaning I will have to duplicate any current or future functions by 5 in order to satisfy each page/day of the week. So 8 function multiplied by 5 days now means 40 functions. I want to avoid this.

The problem...

I'm want to pass the innerHTML of an ID as the sheet/tab name inside getSheetByName(here).

Monday for my example -- I simply need the innerHTML of id=dayofweek "Monday" to be injected into getSheetByName(here)

This way I don't have to make multiple groups of the same functions for each day of the week.

The HTML...

<title id="dayofweek">Monday</title>

Monday page script...

<script>

var theDay = document.getElementById("dayofweek").innerHTML;
Console.log(theDay);

    document.addEventListener('DOMContentLoaded', function () {

        google.script.run.withSuccessHandler(generateMondayDate).getDate();
        google.script.run.withSuccessHandler(generateMondayShift).getShift();
        google.script.run.withSuccessHandler(generateMondayDur).getDur();
        google.script.run.withSuccessHandler(generateMondayNaps).getNaps();
        google.script.run.withSuccessHandler(generateMondayClean).getClean();
        google.script.run.withSuccessHandler(generateMondaySupport).getSupport();
        google.script.run.withSuccessHandler(generateMondayAllClient).getAllClient();
        
        var elems = document.querySelectorAll('.collapsible');
        var instances = M.Collapsible.init(elems);

});

function generateMonday(dataArray) {

    var tbody = document.getElementById("shift-overview-section");

    dataArray.forEach(function (r) {

        var row = document.createElement("tr");
        var col1 = document.createElement("td");
        col1.textContent = r[0];
        var col2 = document.createElement("td");
        col2.textContent = r[1];
        var col3 = document.createElement("td");
        col3.textContent = r[2];
        var col4 = document.createElement("td");
        col4.textContent = r[3];
        var col5 = document.createElement("td");
        col5.textContent = r[4];
        var col6 = document.createElement("td");
        col6.textContent = r[5];

        row.appendChild(col1);
        row.appendChild(col2);
        row.appendChild(col3);
        row.appendChild(col4);
        row.appendChild(col5);
        row.appendChild(col6);

        tbody.appendChild(row);

    });

}

function generateMondayDur(dataArray) {
//Inject stuff I get from getDur() in functions.js
}
function generateMondayNaps(dataArray) {
//Inject stuff I get from getNaps() in functions.js
}
function generateMondayClean(dataArray) {
//Inject stuff I get from getClean() in functions.js
}
function generateMondaySupport(dataArray) {
//Inject stuff I get from getSupport() in functions.js
}
function generateMondayAllClient(dataArray) {
//Inject stuff I get from getAllClient () in functions.js
}
 
</script>
 

Console.log(theDay); shows that the correct info and typeof for "Monday". I just can't seem to inject this value correctly into the functions.js page

My functions.js...

var user = Session.getActiveUser();

function getDate() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();

var filteredData = data.filter(function (employee) { return employee[0]  === currentUser; })
 
Console.log(data);

  return filteredData;
}

function getShift() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}
function getDur() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}
function getNaps() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}
function getClean() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}
function getSupport() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}
function getAllClient() {

  var ss = SpreadsheetApp.openByUrl(masterUrl);
  var ws = ss.getSheetByName(theDay); // "Monday" = document.getElementById("dayofweek").innerHTML;
  var data = ws.getRange(X,X,X,X).getDisplayValues();
 
Console.log(data);

  return data;
}

Log shows the ID is "Monday" and the typeof is a indeed a string, but I'm just too novice when it comes to passing "theDay" variable from my page script to my functions.js.

Answer 1

Description

I've made a simple example of how to get data from several sheets and place in a custom dialog using just one function for each aspect. I do not like the idea of using an event handler for loading data to a sheet and instead I prefer to use an Immediately-Invoked Function Expression (function() {})();

There are sheets name for each day of the week. The callback function for google.script.run gets the data for the current day and the next day.

I think this should give you enough to start with.

The custom dialog for Monday shows the following

Code.gs

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createMenu("My Menu");
  menu.addItem("Test","showTest");
  menu.addToUi();
}

function showTest() {
  try {
    let html = HtmlService.createTemplateFromFile('HTML_Test');
    html = html.evaluate();
    SpreadsheetApp.getUi().showModalDialog(html,"Show Test");
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

function getData(day) {
  try {
    console.log(day);
    let days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName(day);
    let values = sheet.getDataRange().getValues();
    let data = {};
    data.today = values[1];
    sheet = days.indexOf(day)+1;
    sheet = spread.getSheetByName(days[sheet]);
    values = sheet.getDataRange().getValues();
    data.tomorrow = values[1];
    return JSON.stringify(data);
  }
  catch(err) {
    console.log(err);
  }
}

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Today is: <input id="dayofweek" type="text">
    <br>
    <table id="dataTable">
    </table>
    <script>
      (function() {
        try {
          let days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
          let today = new Date();
          today = today.getDay();
          document.getElementById("dayofweek").value = days[today];
          google.script.run.withSuccessHandler( 
            function(data) {
              data = JSON.parse(data);
              let table = document.getElementById("dataTable");
              let row = table.insertRow(0);
              let day = data.today;
              day.unshift("Today");
              day.forEach( (col,index) => { let cell = row.insertCell(index);
                                            cell.innerHTML = col.toString();
                                          }
                          );
              row = table.insertRow();
              day = data.tomorrow;
              day.unshift("Tomorrow");
              day.forEach( col => { let cell = row.insertCell();
                                    cell.innerHTML = col.toString();
                                  }
                          );
            }
          ).withFailureHandler( function (err) {
              alert(err);
            }
          ).getData(days[today]);
        }
        catch(err) {
          alert(err);
        }
      })();
    </script>
  </body>
</html>

Reference

• Spreadsheet Service
• JSON.stringify()/parse()
• google.script.run

Answer 2

Description

Let see if this can explain how to do what you want in a single function rather than redundant functions.

Since I don't know what data you are getting and what you are doing with it I can't build a script that can do exactly what you want. But if this is not enough to help you figure out how to get and set the data values you want, I don't know what more I can do.

Code.gs

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createMenu("My Menu");
  menu.addItem("Test","showTest");
  menu.addToUi();
}

function showTest() {
  try {
    let html = HtmlService.createTemplateFromFile('HTML_Test');
    html = html.evaluate();
    SpreadsheetApp.getUi().showModalDialog(html,"Show Test");
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

function getData(day) {
  try {
    let days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName(day);
    let values = sheet.getDataRange().getDisplayValues();
    let data = {};
    data.date = values[1][1];
    data.shift = values[2][2];  // lets do night shift
    data.duration = values[3][2]
    data.naps = values[4][2]
    data.clean = values[5][2]
    data.support = values[6][2]
    data.client =values[7][1]
    return JSON.stringify(data);
  }
  catch(err) {
    console.log(err);
  }
}

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    Today is: <input id="dayofweek" type="text">
    <br>
    <table id="dataTable">
      <tr>
        <td>Date</td><td></td>
      </tr>
      <tr>
        <td>Shift</td><td></td>
      </tr>
      <tr>
        <td>Duration</td><td></td>
      </tr>
      <tr>
        <td>Naps</td><td></td>
      </tr>
      <tr>
        <td>Clean</td><td></td>
      </tr>
      <tr>
        <td>Support</td><td></td>
      </tr>
      <tr>
        <td>Client</td><td></td>
      </tr>
    </table>
    <script>
      (function() {
        try {
          let days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
          let today = new Date();
          today = today.getDay();
          document.getElementById("dayofweek").value = days[today];
          google.script.run.withSuccessHandler( 
            function(data) {
              data = JSON.parse(data);
              let table = document.getElementById("dataTable");
              let row = table.rows[0];
              let cell = row.cells[1];
              cell.innerHTML = data.date;
              row = table.rows[1];
              cell = row.cells[1];
              cell.innerHTML = data.shift;
              row = table.rows[2];
              cell = row.cells[1];
              cell.innerHTML = data.duration;
              row = table.rows[3];
              cell = row.cells[1];
              cell.innerHTML = data.naps;
              row = table.rows[4];
              cell = row.cells[1];
              cell.innerHTML = data.clean;
              row = table.rows[5];
              cell = row.cells[1];
              cell.innerHTML = data.support;
              row = table.rows[6];
              cell = row.cells[1];
              cell.innerHTML = data.client;
            }
          ).withFailureHandler( function (err) {
              alert(err);
            }
          ).getData(days[today]);
        }
        catch(err) {
          alert(err);
        }
      })();
    </script>
  </body>
</html>