On a previous post I have shown how to use push data.
Since then there have been some changes to it and as for the user end they are shown here: http://www.youtube.com/watch?v=ZEf6JRb5OF8&feature=youtu.be
That video might not be the best one you watched but it is just a draft, final version will come soon enough.
However this post is not about how to make a new procedure, but what happens in the background.
When a new procedure is created
Obviously the app creates a whole lot of panels, labels, buttons and lists. For the most part they are the same as any other app would do.
Here I want to show one part of it (this is used in a few places)
This piece of code is responsible for the list boxes where the user will choose source and destination spreadsheet.
The list has a on change handler which updates the list of sheets in that spreadsheet.
The rest of the user interface is quit simple. The real work starts when the user hits the finish button.
In the first steps all the user-provided data is mapped from various text and list boxes.
After all the data is collected the app makes a new object and saves the provided data in it
Most of this is just saving data, however lines 15-17 might seem a bit odd.
ScriptDb has an integrated function getSize() for query results, but from what I have seen getSize() sometimes returns false results.
That is why the id is calculated manually here.
The next part is to create the trigger that will track changes on the source spreadsheet.
And this was not as easy as it sounds. At the time of writing the app there was no way to determine the source of the onEdit trigger. Yes, there is a e.source for onEdit triggers
but that will return the app’s host spreadsheet, not the spreadsheet where the change occurred. There I used a little trick…
The trick is (and it is not very elegant) I have 10 hardcoded onEdit functions (called editOn)
As You can see all of them have one line of code, they call the “markEdit” function sending as a parameter the function name.
And this is why…
…As I had no way to find out which spreadsheet was edited but wanted to avoid unnecessary database updates at all cost I made a little on edit web.
When a user defines a new process the source spreadsheet gets an onEdit handler assigned (that is IF it does not have one previously assigned).
When a spreadsheet is changed the onEdit (that is editOn) function will fire, and record the change to ScriptDb. Later on, when the app has to update the database
it will only update sheets that are assigned to the function which recorded the change. (the code is bellow in the part about the update of db)
After the tedious process of checking if the source spreadsheet has a onEdit handler then assigning it… Or if the push interval is set to real time assigning the real time trigger the app will go ahead, save the sheet to ScriptDb and finally perform the first data push.
Updating the database
As is stated in the post Big data sets and ScriptDb I had to assume a lot of spreadsheets with a lot of data and I had to find an alternative way of saving the data.
This solution works, and No I am not 100% happy with it and I intend to change it soon enough (working on a library special for cases such as pushData) but the app had to be released and this is what we have now.
Remember the object edited in markEdit, well here the app will query for all of those objects who’s haveChanged attribute is set to true.
That is it will generate a list of all spreadsheets that are changed (at least potentially changed). Again I had no way of finding the actual source of the change so I work with arrays of spreadsheets.
All the spreadsheets associated with the trigger the fired onEdit are remapped in the database. updateDbSingleeSheet has been documented in the post bellow Big data sets and ScriptDb (hack)
Pushing the data
Another trick here, only data that has been changed will be pushed. And again some of it has not been actually changed but is associated with the onEdit handler which recorded a change.
First the app will generate a list of changed spreadsheets.
Then it will cross reference that list with procedures who's push time interval has expired( or plainly it will check if it is time to push the data)
underscoreGS is a awesome library with a bunch of functions for manipulating arrays. (it can be found in the Google Apps Script documentation in the part Notable Libraries)
The app will then go through the list of objects to push, and do the push, sheet by sheet.
The arguments of this function is what the user provided while creating the process. Headers-headers to be pushed, row- the row where the data writing should start, column- the column where the data writing should start, keepEmpty-is weather the empty rows from the source sheet should be kept in the destination sheet or filed with the next available non-empty row.
First thing the function does is assemble all the pieces of data from ScriptDb (while saving the data it might be divided into smaller pieces)
Then it will clear the columns in which the data will be written. (why? well if you had 100 entries previously and now have only 50 this will delete the remaining rows)
Finally the data is written and the app will proceed to the next process.
Since then there have been some changes to it and as for the user end they are shown here: http://www.youtube.com/watch?v=ZEf6JRb5OF8&feature=youtu.be
That video might not be the best one you watched but it is just a draft, final version will come soon enough.
However this post is not about how to make a new procedure, but what happens in the background.
When a new procedure is created
Obviously the app creates a whole lot of panels, labels, buttons and lists. For the most part they are the same as any other app would do.
Here I want to show one part of it (this is used in a few places)
1 var folder=DocsList.getFileById(ss.getId()).getParents()[0];2 var files=folder.getFilesByType("spreadsheet");3 for(var i=0; i<files.length; i++)4 sourceSpreadsheet.addItem(files[i].getName(), files[i].getId());
This piece of code is responsible for the list boxes where the user will choose source and destination spreadsheet.
The list has a on change handler which updates the list of sheets in that spreadsheet.
The rest of the user interface is quit simple. The real work starts when the user hits the finish button.
In the first steps all the user-provided data is mapped from various text and list boxes.
1 var newPush={}; 2 3 4 newPush.sourceSpreadsheetName=sourceSS.getName(); 5 newPush.sourceSpreadsheetId=sourceSS.getId(); 6 newPush.sourceSheetName=sourceSheetName; 7 newPush.headers=headers; 8 newPush.destinationSpreadsheetName=destinationSS.getName(); 9 newPush.destinationSpreadsheetId=destinationSS.getId();10 newPush.destinationSheet=destinationSheetName;11 newPush.row=startRow;12 newPush.column=startColumn;13 newPush.time=timeInterval;14 newPush.keepEmpty=e.parameter.keepEmpty;15 var id=1;16 while(db.query({type:"pushData", pushId:id}).hasNext()==true)17 id++;18 newPush.pushId=id;19 newPush.lastPush=new Date().toString();20 newPush.success="Done";21 newPush.type="pushData";22 var realTime=false
23 if(timeInterval==0){24 newPush.realTime="true";25 realTime=true;26 }27 else
28 newPush.realTime="false";
After all the data is collected the app makes a new object and saves the provided data in it
Most of this is just saving data, however lines 15-17 might seem a bit odd.
ScriptDb has an integrated function getSize() for query results, but from what I have seen getSize() sometimes returns false results.
That is why the id is calculated manually here.
The next part is to create the trigger that will track changes on the source spreadsheet.
And this was not as easy as it sounds. At the time of writing the app there was no way to determine the source of the onEdit trigger. Yes, there is a e.source for onEdit triggers
but that will return the app’s host spreadsheet, not the spreadsheet where the change occurred. There I used a little trick…
The trick is (and it is not very elegant) I have 10 hardcoded onEdit functions (called editOn)
1 function editOn1(){ 2 3 markEdit("editOn1"); 4 5 } 6
7 function editOn2(){ 8 9 markEdit("editOn2");10 11 }12
13 function editOn3(){14 15 markEdit("editOn3");16 17 }18
19 function editOn4(){20 21 markEdit("editOn4");22 23 }24
25 function editOn5(){26 27 markEdit("editOn5");28 29 }30
31 function editOn6(){32 33 markEdit("editOn6");34 35 }36
37 function editOn7(){38 39 markEdit("editOn7");40 41 }42
43 function editOn8(){44 45 markEdit("editOn8");46 47 }48
49 function editOn9(){50 51 markEdit("editOn9");52 53 }54
55 function editOn10(){56 57 markEdit("editOn10");58 59 }60
61 function markEdit(root){62 63 var db=ScriptDb.getMyDb();64 65 var results=db.query({type:"editHandler", editFunction:root});66 67 var obj=results.next();68 obj.haveChanged="true"; 69 db.save(obj);70 71 72 }
As You can see all of them have one line of code, they call the “markEdit” function sending as a parameter the function name.
And this is why…
…As I had no way to find out which spreadsheet was edited but wanted to avoid unnecessary database updates at all cost I made a little on edit web.
When a user defines a new process the source spreadsheet gets an onEdit handler assigned (that is IF it does not have one previously assigned).
When a spreadsheet is changed the onEdit (that is editOn) function will fire, and record the change to ScriptDb. Later on, when the app has to update the database
it will only update sheets that are assigned to the function which recorded the change. (the code is bellow in the part about the update of db)
After the tedious process of checking if the source spreadsheet has a onEdit handler then assigning it… Or if the push interval is set to real time assigning the real time trigger the app will go ahead, save the sheet to ScriptDb and finally perform the first data push.
Updating the database
As is stated in the post Big data sets and ScriptDb I had to assume a lot of spreadsheets with a lot of data and I had to find an alternative way of saving the data.
This solution works, and No I am not 100% happy with it and I intend to change it soon enough (working on a library special for cases such as pushData) but the app had to be released and this is what we have now.
1 function updateProces(){ 2 3 var db=ScriptDb.getMyDb(); 4 5 var changedSS=db.query({type:"editHandler", haveChanged:"true"}); 6 7 8 while(changedSS.hasNext()){ 9 10 var editObj=changedSS.next();11 12 var spreadsheets=Utilities.jsonParse(editObj.spreadsheets);13 14 for(var k=0; k<spreadsheets.length; k++){15 16 var ss=SpreadsheetApp.openById(spreadsheets[k]); 17 var sheets=ss.getSheets();18 19 for(var l=0; l<sheets.length; l++){20 21 var sheet=sheets[l]; 22 var hasProcedure=db.query({type:"pushData", sourceSheetName:sheet.getName()});23 if(hasProcedure.hasNext()==true)24 updateDbSingleSheet(ss, sheet)25 26 }27 }28 29 }30 31 }32
Remember the object edited in markEdit, well here the app will query for all of those objects who’s haveChanged attribute is set to true.
That is it will generate a list of all spreadsheets that are changed (at least potentially changed). Again I had no way of finding the actual source of the change so I work with arrays of spreadsheets.
All the spreadsheets associated with the trigger the fired onEdit are remapped in the database. updateDbSingleeSheet has been documented in the post bellow Big data sets and ScriptDb (hack)
Pushing the data
Another trick here, only data that has been changed will be pushed. And again some of it has not been actually changed but is associated with the onEdit handler which recorded a change.
First the app will generate a list of changed spreadsheets.
1 while(changedSS.hasNext()){2 3 var editObj=changedSS.next(); 4 mergeArray(listOfChangedSS,Utilities.jsonParse(editObj.spreadsheets));5 }6
Then it will cross reference that list with procedures who's push time interval has expired( or plainly it will check if it is time to push the data)
1 if( ( (timeStart-lastPush)/3600000)+0.25>=obj.time){
2 if(underscoreGS._contains(listOfChangedSS, obj.sourceSpreadsheetId)==true)3 objectsToPush.push(obj);4 }
underscoreGS is a awesome library with a bunch of functions for manipulating arrays. (it can be found in the Google Apps Script documentation in the part Notable Libraries)
The app will then go through the list of objects to push, and do the push, sheet by sheet.
1 unction writteData(headers, sourceSheet, destinationSheet, sourceSpreadsheet, row, column, keepEmpty){ 2 3 var db=ScriptDb.getMyDb(); 4 var items=db.query({itemType:"sheetData", spreadsheetId:sourceSpreadsheet.getId(), sheetName:sourceSheet}).sortBy("itemId", db.NUMERIC); 5 6 var completeData=[]; 7 for(var i=0; i<headers.length; i++){ 8 9 completeData[i]=[];10 completeData[i][0]=headers[i];11 }12 13 while(items.hasNext()){14 15 var thisItem=items.next();16 for(var i=0; i<headers.length; i++){17 if(!completeData[i])18 completeData[i]=[];19 20 mergeArray(completeData[i], Utilities.jsonParse(thisItem[headers[i]]));21 }22 23 }24 25 completeData=ArrayLib.transpose(completeData);26 27 if(keepEmpty=="false")28 completeData=findEmpty(completeData);29 30 var mR=destinationSheet.getMaxRows();31 var lR=destinationSheet.getLastRow();32 if(lR>completeData.length)33 destinationSheet.getRange(row, column, lR, completeData[0].length).clear();34 destinationSheet.getRange(row,column,completeData.length, completeData[0].length).setValues(completeData); 35 }36
The arguments of this function is what the user provided while creating the process. Headers-headers to be pushed, row- the row where the data writing should start, column- the column where the data writing should start, keepEmpty-is weather the empty rows from the source sheet should be kept in the destination sheet or filed with the next available non-empty row.
First thing the function does is assemble all the pieces of data from ScriptDb (while saving the data it might be divided into smaller pieces)
Then it will clear the columns in which the data will be written. (why? well if you had 100 entries previously and now have only 50 this will delete the remaining rows)
Finally the data is written and the app will proceed to the next process.
