I use Google Sheets to help track pay to our contractors. Sometimes the work order has two or more contractors and rather than using a new row for the same information just a different contractor and pay amount, we separate the contractor's names as well as their pay with a /. So for instance, in the contractor column we have:
John Doe/Frank
And in the pay column we have:
468/65
The pay is respective, so John is owed $468 and Frank $65.
What I'm trying to do is setup a separate sheet for each of the contractor's so that they can see in almost real time the status of their own order's payments without jeopardizing other contractor's information. I'm using the Google Apps Scripts to transfer information and for the most part it is working correctly. The problem I'm having is when the script reaches a row where there are two contractors for an order. Strangely, John's name and pay are written to the correct corresponding array (johnDest), but then Frank's name and pay overwrites the John's previous entry for this row. I setup the function for writing to the arrays to be dependent on the individual name of the contractor I'm passing in, not the full value of the contractor's name cell. I start with arrays for each of the contractors the spreadsheet id's for copying:
var johnDest = [];
var john = "this-is-the-link-to-johns-sheet";
var frankDest = [];
var frank = "this-is-the-link-to-franks-sheet";
Then I enter a loop to add the row values to these arrays so that in the end I can write the array to their respective spreadsheets:
function exportData() {
var columnM = thisWorksheet.getRange(2, 1, thisWorksheet.getLastRow(), thisWorksheet.getLastColumn());
var mValues = columnM.getValues();
for(var i = 0; i < mValues.length; i++){
var mName = mValues[i][0];
if(mName.indexOf('/') > -1){ //If contractor name column contains a '/' split it.
var names = mName.split('/');
var pays = mValues[i][1];
pays = pays.split('/');
for(var g = 0; g < names.length; g++){ //For each name in split array, get name and corresponding pay to add to array.
var cName = names[g];
mValues[i][0] = names[g];
mValues[i][1] = pays[g];
Logger.log(cName); //To log the contractor's name that I am currently working with in the loop.
switchcontractor(cName, mValues[i]);
}
}else{
switchcontractor(mName, mValues[i]);
}
}
copyData(john, johnDest); //Once loop is through and arrays are completed, copy data to respective sheets.
copyData(frank, frankDest);
}
Here is the switchcontractor function:
function switchcontractor(cName, contValues){
Logger.log(johnDest.length + ' ' + cName); //Log the length of johnDest and the current contractor in the loop.
if(cName == 'John'){
johnDest.push(contValues);
}else if(cName == 'Frank'){
frankDest.push(contValues);
Logger.log(johnDest.length + ' ' + cName + ' ' + contValues);
}
}
If I run the script as is, the Logger shows this information:
[14-11-13 16:16:01:843 MST] John //Current contractor I'm working with
[14-11-13 16:16:01:843 MST] 23 John //Current length of johnDest before row information is pushed to it and contractor's name
[14-11-13 16:16:01:844 MST] 24 John John,468 //Updated length of johnDest, current contractor, and row information
[14-11-13 16:16:01:844 MST] Frank //Current contractor
[14-11-13 16:16:01:844 MST] 24 Frank //Current length of johnDest and contractor's name
[14-11-13 16:16:01:845 MST] 24 Frank Frank,65 //Length stays the same for johnDest, current contractor and row information
To verify that the script is actually in the switchcase for Frank, I commented out the Logger line just after the frankDest.push(contValues); line above and I'm given this:
[14-11-13 16:22:52:684 MST] John
[14-11-13 16:22:52:684 MST] 23 John
[14-11-13 16:22:52:684 MST] 24 John John,468
[14-11-13 16:22:52:685 MST] Frank
[14-11-13 16:22:52:685 MST] 24 Frank
//The Logger line that was commented out in the switchcase for Frank doesn't show, so obviously I'm in that case, right?
However, when Frank's name comes through the loop, it is written to the johnDest array as well as Frank's own array. The end result in John's sheet is this:
John | 55 //This is an example of a previous row
Frank | 65 //This is the row in question
John | 125 //This is an example of a following row
This is what is on Frank's sheet:
Frank | 25 //This is an example of a previous row
Frank | 65 //This is the row in question
Frank | 15 //This is an example of a following row
I'm very confused as to why Frank's information is written to John's array AND his own for the row. Any help is greatly appreciated. Thank you!
