1

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');
}
4
  • 1
    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? Commented Aug 29, 2019 at 12:05
  • 1
    @TanyaGupta that fixed it! thanks, could you tell me what the reason is? Commented Aug 29, 2019 at 12:21
  • Great! Will post the answer below Commented Aug 29, 2019 at 12:22
  • 1
    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) Commented Aug 29, 2019 at 12:28

2 Answers 2

3

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

Sign up to request clarification or add additional context in comments.

Comments

1

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.