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');
}
iinrowToObjectand the main function. Could you change theiin therowToObjectfunction tovar jand let me know what happens?varwithin the function where you are going to use that identifier. This means you,forloops! (More recent JS versions should useletorconstas appropriate)