Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsglobal-variables

how to increment a global variable in JavaScript every time a function runs in Google Apps Script?

This adapted code:

var count = 0;


function AddRecord(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("data");
  var cell = sheet.getRange("F5");
  cell.setValue(name); 
  cell = sheet.getRange("B2");
  cell.setValue(count);
  count = count + 1;
}

function startForm() {
  var form = HtmlService.createHtmlOutputFromFile('AddForm');
  SpreadsheetApp.getUi().showModalDialog(form, 'Add Record');


}

function addMenu() {
  var menu = SpreadsheetApp.getUi().createMenu('Custom');
  menu.addItem('Add Record Form', 'startForm');
  menu.addToUi();

}

function onOpen(e) {

  addMenu();
}

works as expected, in that it writes the expected values, name and count, to their respective cells. However, the value for count remains unchanged. How is the count variable incremented every time the AddRecord function executes?

This const counter = ((count = 0) => () => count++)(); would seem to be at least the shortest solution suggested.

Solution

  • Modification points:

    • When a function AddRecord(name) of Google Apps Script is run from google.script.run.AddRecord(name) of Javascript, unfortunately, var count = 0; is always run. By this, count is not changed from 0 every run of the function, and the value of count is not kept. I thought that this is the reason for your issue.

    If you want to keep the count and when AddRecord(name) is run, you want to count up the value of count, how about the following modification? In this modification, I used PropertiesService.

    Modified script:

    In this modification, the value of count is kept even when the dialog is closed.

    function AddRecord(name) {
  var p = PropertiesService.getScriptProperties();
  var count = p.getProperty("count")
  count = count ? Number(count) : 0;

  // This is your current script.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("data");
  var cell = sheet.getRange("F5");
  cell.setValue(name);
  cell = sheet.getRange("B2");
  cell.setValue(count);
  count = count + 1;


  // Browser.msgBox(count); // If you use this, when "AddRecord" is run, you can see the current value of "count" in a dialog.

  p.setProperty("count", count);
}

// When you want to reset the value of "count", please run this function.
function resetCounter() {
  PropertiesService.getScriptProperties().deleteProperty("count");
}

function startForm() {
  var form = HtmlService.createHtmlOutputFromFile('AddForm');
  SpreadsheetApp.getUi().showModalDialog(form, 'Add Record');
}
    • In this modification, when AddRecord is run from Javascript, "count" is retrieved from PropertiesService and the value is increased and the updated value is stored in PropertiesService. By this, the value of "count" is kept.

    Note:

    • At the above-modified script, only Google Apps Script is used. As another direction, if you can also use the HTML side, how about the following modification? In this modification, the value of count is cleared when the dialog is closed.

      • Google Apps Script side:

          function AddRecord(name, count) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("data");
    var cell = sheet.getRange("F5");
    cell.setValue(name);
    cell = sheet.getRange("B2");
    cell.setValue(count);
    count = count + 1;
    return count;
  }

  function startForm() {
    var form = HtmlService.createHtmlOutputFromFile('AddForm');
    SpreadsheetApp.getUi().showModalDialog(form, 'Add Record');
  }

      • HTML side: This HTML is from your showing URL.

          <!DOCTYPE html>
  <html>
    <head>
      <base target="_top">
      <script>
      let count = 0;
      function AddRow()
      {
      var name = document.getElementById("name").value;
      google.script.run.withSuccessHandler(e => {
        count = e;
        console.log(count);
      }).AddRecord(name, count);
      }
      </script>
    </head>
    <body>
      Name:  <input type="text" id="name" />
      <input type="button" value="Add" onclick="AddRow()" />
    </body>
  </html>

      • When this script is run, when the button is clicked, count is increased. You can see it in the console of the browser.

    Reference: