Question

0

24

Views

Google Apps Script web app. Use variable outside of an if forEach statement

I'm new to Javascript, I thought I was going ok but I can't figure out this problem.

I'm using Google Apps script to get data from my Google Sheets doc and if a name in column A matches "Sundries" then show the price in Column B.

My script works when I use alert inside the if forEach function but when I move alert outside of it, it breaks. I throws up alerts saying undefined, undefined, undefined, the correct price then undefined, undefined again.

I'm guessing it's something to do with forEach but I don't know away around it.

Here's the section of script that is my problem.

      document.addEventListener("DOMContentLoaded", afterSidebarLoads); 

      //get the data from Google Sheets
      function getRates() {
        const sheet = SpreadsheetApp.openById("fwffwfwefewdwedwedwedwedwedwedwed").getSheetByName("tab name");
        return sheet.getRange(15, 1, sheet.getLastRow()-14, 2).getValues();
      }
      
      // runs the script
      function afterSidebarLoads() { // Function Part: 1
        google.script.run.withSuccessHandler(getSundriesyRate).getRates(); 
      }

      // here's my problem
      function getSundriesyRate(arrayOfArrays){ // Function Part: 2
        var sundriesRate = document.getElementById("sundries-rate");

        arrayOfArrays.forEach(function(r){ // r= one of the lines in the aray
          var div = document.createElement("div");
          if (r[0] === "Sundries") { // this does match 
            var dhello = r[1].toLocaleString("en-GB", {style: "currency", currency: "GBP", minimumFractionDigits: 2});
            alert(dhello); // works
          } else {

          }
          alert(dhello); // doesn't work
        });
      }

Thanks

about 1 month ago ·

Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Loading

Accepted Answer · 2022-07-04T07:36:34.644Z

By create a variable outside the forEach loop and pass the value into the variable. then you will be able to use this variable outside the loop. If you are expecting to return multiple values, you should use a array or dictionaries to store the data.

 document.addEventListener("DOMContentLoaded", afterSidebarLoads);
  //get the data from Google Sheets
  function getRates() {
    const sheet = SpreadsheetApp.openById("fwffwfwefewdwedwedwedwedwedwedwed").getSheetByName("tab name");
    return sheet.getRange(15, 1, sheet.getLastRow()-14, 2).getValues();
  }
  
  // runs the script
  function afterSidebarLoads() { // Function Part: 1
    google.script.run.withSuccessHandler(getSundriesyRate).getRates(); 
  }

  // here's my problem
  function getSundriesyRate(arrayOfArrays){ // Function Part: 2
    var sundriesRate = document.getElementById("sundries-rate");
    var dhello;
    arrayOfArrays.forEach(function(r){ // r= one of the lines in the aray
      var div = document.createElement("div");
      if (r[0] === "Sundries") { // this does match 
        dhello = r[1].toLocaleString("en-GB", {style: "currency", currency: "GBP", minimumFractionDigits: 2});
        alert(dhello); // works
      } else {

      }
      //alert(dhello); // doesn't work (it's return undefined because in this forEach loop, it will go through every line of the array and it does not find the match , it returns nothing.)  
    });
     alert(dhello);  // you can access this variable here. 
  }