Take the number from two different functions and add them together in another function (Unique to Go-CodePudding

I am trying to create a simple function that will add one number to another to get a total amount, seems easy right? Thats what I thought although I am new to javascript and I believe Google Apps Script could also playing with me.

Here is a stripped down code of that exact function that is WORKING with manually inputted figures.

<!DOCTYPE html>
<html>
  <head>
    <title>Calculator - V1</title>
  </head>
  <body>

    <script>

      function showHourlyRate() {
        var oneFunc = 1
        console.log(oneFunc) //result is 5.00
        // alert(oneFunc);
        return oneFunc
      };

      function showMileageRate() {
        var twoFunc = 1
        console.log(twoFunc) //result is 5.00
        // alert(twoFunc);
        return twoFunc
      };
      function showCombinedFigure() {
        var twoFunc = showHourlyRate()   showMileageRate()
        alert(twoFunc);
      };

      document.addEventListener("DOMContentLoaded", showCombinedFigure);
    </script>
  </body>

</html>

That takes the showHourlyRate and showMileageRate figures and adds them together to make 2, perfect.

I need to get those figures from a Google Sheets doc so (using google apps script application to code) I have 3 files, one is the default Code.gs file. I have then created a functions.gs file and an index.html file.

Here's my Code.gs file

function doGet(e) {
  var htmlOutput =  HtmlService.createTemplateFromFile('index');
 
  return htmlOutput.evaluate();
}

I believe you need this in order for it all to run and it basically just says to use the index.html file.

Here's my functions.gs file

function getHourlyRate() {
  const sheet = SpreadsheetApp.openById("643876537465743653456").getSheetByName("Working Hours");
  const data = sheet.getRange(2, 1, sheet.getLastRow()-1, 8).getValues();
  const filteredData = data.filter(r => r[0] === "Sundries");

  return filteredData.length === 0 ? 0 : filteredData.reduce((subtotal, r) => subtotal   r[1], 0).toFixed(2);
  
}

function getMileageRate() {
  const sheet = SpreadsheetApp.openById("643876537465743653456").getSheetByName("Working Hours");
  const data = sheet.getRange(2, 1, sheet.getLastRow()-1, 8).getValues();
  const filteredData = data.filter(r => r[0] === "Mileage");

  return filteredData.length === 0 ? 0 : filteredData.reduce((subtotal, r) => subtotal   r[1], 0).toFixed(2);
  
}

This is where I get the google sheets information, it finds the doc by ID, then finds the sheet by name, selects the rows I want to search then filters the results based on my chosen word.

Finally my html code:

<!DOCTYPE html>
<html>
  <head>
    <title>Calculator - V1</title>
  </head>
  <body>
    <script>

      function afterSidebarLoads() {
        google.script.run.withSuccessHandler(showHourlyRate).getHourlyRate(); 
        google.script.run.withSuccessHandler(showMileageRate).getMileageRate();
      }

      function showHourlyRate(hourlyRate) {
        var oneFunc = hourlyRate
        console.log(oneFunc) //result is 5.00
        //alert(oneFunc);
        return oneFunc
      };

      function showMileageRate(mileageRate) {
        var twoFunc = mileageRate
        console.log(twoFunc) //result is 5.00
        //alert(twoFunc);
        return twoFunc
      };
      function showCombinedFigure() {
        var twoFunc = showHourlyRate()   showMileageRate()
        alert(twoFunc);
      };

      document.addEventListener("DOMContentLoaded", afterSidebarLoads);
      document.addEventListener("DOMContentLoaded", showCombinedFigure);
    </script>
  </body>

</html>

Now it no longer works. The alerts for the showHourlyRate() and showMileageRate() work (when activated) and shows the correct figures. The alert for showCombinedFigure() no longer works, I get a NAN error, if I remove ' showMileageRate()' like so:

      function showCombinedFigure() {
        var twoFunc = showHourlyRate()   showMileageRate()
        alert(twoFunc);
      };

I get undefined error.

I'm not exactly sure what this function does but is this the culprit? I followed a guide to get me to the point of reading the data from the sheets doc but now I need to move further along.

      function afterSidebarLoads() {
        google.script.run.withSuccessHandler(showHourlyRate).getHourlyRate(); 
        google.script.run.withSuccessHandler(showMileageRate).getMileageRate();
      }

CodePudding user response：

The values returned by showMileageRate and showHourlyRate aren't being stored anywhere, you'll want to change your functions to store them globally:

<script>
      let oneFunc
      let twoFunc

      function afterSidebarLoads() {
        // get mileage rate
        google.script.run.withSuccessHandler(mileageRateHandler).getMileageRate()

      }
      
      function mileageRateHandler(mileageRate) {
        // set gloabal oneFunc
        oneFunc = mileageRate

        // get hourly rate
        google.script.run.withSuccessHandler(hourlyRateHandler).getHourlyRate()
      }

      function hourlyRateHandler(hourlyRate) {
        // set global twoFunc
        twoFunc = hourlyRate
        
        // call showCombinedFigure after both Apps Script methods have completed
        showCombinedFigure()
      }

      function showCombinedFigure() {
        var result = oneFunc   twoFunc
        alert(result);
      };

      document.addEventListener("DOMContentLoaded", afterSidebarLoads);
  </script>

Also you need to make sure that showCombinedFigure runs only after the Apps Script functions have returned, as google.script.run is an asynchronous function and returns after the DOMContentLoaded event is fired.