google scripts; function within for loop stops it

Question

Google sheets: I created a script which detects the row which is changed and send the changes to slack.

I created a for loop to loop through the changed rows in the cache. The for loop stops at the first entry when the rowToObject() is called. If I comment that function the loop works as expected.

What could be the reason?

// send all rows in cache to slack
function cacheToSlack() {
  var changes = JSON.parse(cache.get('changes'));
  Logger.log(changes);
  Logger.log(changes.length);


  for (i = 0; i < changes.length; i++) {

    // get edit range
    var row = changes[i]; 
    var rowValues = sheet.getRange(row, 1, 1, headerNum).getValues()[0];
    Logger.log(row);
    Logger.log(rowValues);
    rowToObject(rowValues);
  }

}

full code

// created cache for saving changed rows
var cache = CacheService.getDocumentCache();

// sheet and header data
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
var headers = sheet.getRange("1:1").getValues();
var headerNum = headers[0].length;

// track changed rows
function onChange(e) {
  var currentCache = cache.get('changes') || "[]";

  Logger.log('current');
  Logger.log(currentCache);

  Logger.log('row');
  Logger.log(e.range.getRow());

  var rowNum = e.range.getRow();
  var update = JSON.parse(currentCache).push(rowNum);
  cache.put('changes', JSON.stringify(update));

  Logger.log('change');
  Logger.log(cache.get('changes'));
}

function testCache() {
  // cache cannot save arrays
  cache.put('changes', JSON.stringify([156,157]));
  cacheToSlack();
}

// send all rows in cache to slack
function cacheToSlack() {
  var changes = JSON.parse(cache.get('changes'));
  Logger.log(changes);
  Logger.log(changes.length);


  for (i = 0; i < changes.length; i++) {

    // get edit range
    var row = changes[i];

    var rowValues = sheet.getRange(row, 1, 1, headerNum).getValues()[0];
    Logger.log(row);
    Logger.log(rowValues);
    rowToObject(rowValues);
  }

}

function rowToObject(row) {
  // create data object with headers as keys
  var data = {};

  for (i = 0; i < headerNum; ++i) {
    var header = headers[0][i];
    data[header] = row[i];
  }

  postToSlack(data);
}


function postToSlack(data) {
  Logger.log(data);
  // Create payload object
  var payloadObject = {
    blocks: [
      {
        type: "section",
        text: {
          type: "mrkdwn",
          text:
            "You have a new *<|Change or learning>*"
        }
      },
      {
        type: "section",
        fields: [
          {
            type: "mrkdwn",
            text: "*Channel:*\n" + data.channel
          },
          {
            type: "mrkdwn",
            text: "*Where:*\n" + data.where
          },
          {
            type: "mrkdwn",
            text: "*dateFrom:*\n" + data.dateFrom
          },
          {
            type: "mrkdwn",
            text: "*dateTo:*\n" + data.dateTo
          },
          {
            type: "mrkdwn",
            text: "*description:*\n" + data.description
          },
          {
            type: "mrkdwn",
            text: "*learning:*\n" + data.learning
          },
          {
            type: "mrkdwn",
            text: "*impact:*\n" + data.impact
          }
        ]
      }
    ]
  };

  // Stringify payload
  var payload = {
    payload: JSON.stringify(payloadObject)
  };

  // Build request
  var options = {
    method: "post",
    payload: payload
  };

  // Send to Slack
  Logger.log(options);
  //UrlFetchApp.fetch(SLACK_WEBHOOK_POST_URL, options);
}

function clearCache() {
  cache.remove('changes');
}

It *could* be a scoping issue because you have the same `i` in `rowToObject` and the main function. Could you change the `i `in the `rowToObject` function to `var j ` and let me know what happens? — Tanya Gupta, Aug 29 '19 at 12:05
@TanyaGupta that fixed it! thanks, could you tell me what the reason is? — Christoph, Aug 29 '19 at 12:21
Global variables strike again! **ALWAYS** declare variables as scoped, e.g. use `var` within the function where you are going to use that identifier. This means you, `for` loops! (More recent JS versions should use `let` or `const` as appropriate) — tehhowch, Aug 29 '19 at 12:28

score 3 · Accepted Answer · answered Aug 29 '19 at 12:35

Changing i to var i or a different variable name such as var j in the local function rowToObject should fix the error.

The above error is due to a scoping issue. Scope is about the accessibility of variables (and functions) and specifies the context in which the variable is being executed.

The variable i in rowToObject has not been initialized (i.e. set to var i =0 instead of just i). Initialization of i makes i local and not global. When i is not initialized inside the local function, the scope is polluted and it gets treated as having a global scope since you have the same variable i initialized incacheToSlack

See MDN Reference for more

score 1 · Answer 2 · answered Aug 29 '19 at 12:30

The problem is that you do not define your counter `i` as `var`, making it thus a global variable.

This leads to conflicts when you redefine i in rowToObject(). You can avoid this problem by defining the variables locally with var i.

See here for more information about the usage of var.

google scripts; function within for loop stops it

2 Answers2

The problem is that you do not define your counter i as var, making it thus a global variable.

The problem is that you do not define your counter `i` as `var`, making it thus a global variable.