I have a google apps script that is pulling data from a google apps sheet via a For loop then puts each row into an array to prep for exporting to an external API. My problem is that the script skips ahead/over some entries and I cannot understand why.
Here's the entire code:
function prepEmails() {
var ss = SpreadsheetApp.getActive();
var s = ss.getSheetByName("Emails");
var sLastRow = s.getLastRow();
var scriptProperties = PropertiesService.getScriptProperties();
var logCount = scriptProperties.getProperty('EmailCount');
Logger.log("63 logCount = "+logCount);
var url = "https://apitoexportto.com";
var recordType = "email";
var logKey = "mailCount"
var activityDesc = "######"
if (logCount === null) {
scriptProperties.setProperty('EmailCount', 0);
logCount = 0;
}
Logger.log("68 logCount = "+logCount);
var msgs = sLastRow - logCount;
var arr = new Array();
for (var i=0; i<msgs-1; i++) {
Logger.log("78 inside FOR loop");
var nextRow = logCount+1; //+1 to skip header row
var msg = s.getRange(nextRow+1, 1, 1, 6).getValues(); //+1 to skip to next row
var payload = {
'type' : "TimeEntry",
'matter_id' : msg[0][0],
'price' : msg[0][1],
'date' : msg[0][2],
'note' : msg[0][3],
'quantity' : msg[0][4],
'activity_description_id': activityDesc
};
arr.push(payload);
var logCount = parseInt(logCount)+1;
}
Logger.log("95 logCount = "+logCount);
if (i>0) {
exportEmails(url, recordType, arr, logKey, activityDesc);
}
}
Here's the execution transcript: EDIT: I don't have enough rep to post images yet, so here's a link to the transcript. Execution Transcript and I'll paste it below as well.
[14-12-18 08:20:44:782 CST] Starting execution
[14-12-18 08:20:44:814 CST] SpreadsheetApp.getActive() [0 seconds]
[14-12-18 08:20:44:840 CST] Spreadsheet.getSheetByName([Emails]) [0.024 seconds]
[14-12-18 08:20:44:859 CST] Sheet.getLastRow() [0.019 seconds]
[14-12-18 08:20:44:861 CST] PropertiesService.getScriptProperties() [0 seconds]
[14-12-18 08:20:44:867 CST] (class).getProperty([EmailCount]) [0.004 seconds]
[14-12-18 08:20:44:868 CST] Logger.log([63 logCount = 98.0, []]) [0 seconds]
[14-12-18 08:20:44:868 CST] Logger.log([68 logCount = 98.0, []]) [0 seconds]
[14-12-18 08:20:44:869 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:870 CST] Sheet.getRange([98, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:913 CST] Range.getValues() [0.042 seconds]
[14-12-18 08:20:44:914 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:914 CST] Sheet.getRange([101, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:916 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:917 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:918 CST] Sheet.getRange([102, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:919 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:920 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:920 CST] Sheet.getRange([103, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:922 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:923 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:924 CST] Sheet.getRange([104, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:925 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:926 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:927 CST] Sheet.getRange([105, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:928 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:929 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:930 CST] Sheet.getRange([106, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:931 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:932 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:933 CST] Sheet.getRange([107, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:934 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:935 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:936 CST] Sheet.getRange([108, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:937 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:938 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:939 CST] Sheet.getRange([109, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:940 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:941 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:942 CST] Sheet.getRange([110, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:943 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:944 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:945 CST] Sheet.getRange([111, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:946 CST] Range.getValues() [0 seconds]
[14-12-18 08:20:44:947 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:948 CST] Sheet.getRange([112, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:949 CST] Range.getValues() [0.001 seconds]
[14-12-18 08:20:44:950 CST] Logger.log([78 inside FOR loop, []]) [0 seconds]
[14-12-18 08:20:44:951 CST] Sheet.getRange([113, 1, 1, 6]) [0 seconds]
[14-12-18 08:20:44:959 CST] Range.getValues() [0.008 seconds]
[14-12-18 08:20:44:960 CST] Logger.log([95 logCount = 112, []]) [0 seconds]
[14-12-18 08:20:44:961 CST] Logger.log([in exportEmails function, []]) [0 seconds]
As you can see, it starts grabbing the values from row 98. This is as expected based on the logCount (a script property keeping track of the total number of exports and picking up at the appropriate row based on that number). But then it skips over 99 and 100 and grabs from row 101. There's nothing unusual about the data in these rows. (the rows were not blank or anything weird like that). I confirmed that the values on rows 99 and 100 were not exported via the next function. They were just skipped.
I'm relatively new to javascript (and completely self-taught), but this seems like a simple for loop to me. No continues, no breaks, no returns. I can't understand where, or why, it would skip ahead like this.
Can anyone tell me why these rows would get skipped?
I would change this line:
var nextRow = logCount+1; //+1 to skip header row
To:
nextRow = nextRow + 1; //+1 to skip header row
And give nextRow an initial value ABOVE the For loop:
var nextRow = logCount - 1; //initialize nextRow
for (var i=0; i<msgs-1; i++) {
Logger.log("78 inside FOR loop. Value of nextRow: " + nextRow);
nextRow = nextRow + 1; //+1 to skip header row
var msg = s.getRange(nextRow+1, 1, 1, 6).getValues(); //+1 to skip to next row
var payload = {
'type' : "TimeEntry",
'matter_id' : msg[0][0],
'price' : msg[0][1],
'date' : msg[0][2],
'note' : msg[0][3],
'quantity' : msg[0][4],
'activity_description_id': activityDesc
};
Also see the change I made to the Logger.log("78 inside FOR loop. Value of nextRow: " + nextRow); statement.