function getNewData() { //deleting triggers var triggers = ScriptApp.getProjectTriggers(); for (var i = 0; i < triggers.length; i++) { if (triggers[i].getHandlerFunction()=='getNewData'){ ScriptApp.deleteTrigger(triggers[i]); } } //max running time = 5.5 min var MAX_RUNNING_TIME = 330000; var startTime= (new Date()).getTime(); //get cache var cache = CacheService.getUserCache(); var downloaded =JSON.parse(cache.get('downloaded')); var compared =JSON.parse(cache.get('compared')); //start if (downloaded==1 && compared!=1){ //folder var Folder = DriveApp.getFoldersByName('theFolder').next(); var FolderId = licitacionesFolder.getId(); //call old_spreadsheet var searchFor ="fullText contains 'sheet_old' and '" + FolderId + "' in parents"; var files = DriveApp.searchFiles(searchFor); var old_file = files.next(); var old_spreadsheet = SpreadsheetApp.openById(old_file.getId()); var old_sheet = old_spreadsheet.getSheets()[0]; var old_array = old_sheet.getDataRange().getValues(); //call spreadsheet var searchFor ="fullText contains 'sheet' and '" + FolderId + "' in parents"; var files = DriveApp.searchFiles(searchFor); var file = files.next(); var spreadsheet = SpreadsheetApp.openById(old_file.getId()); var sheet = spreadsheet.getSheets()[0]; var array = sheet.getDataRange().getValues(); Logger.log(array.length+'::'+old_array.length); // Column var searchString = 'NAME'; var RC = getColumn(array,searchString); var Row = RC.Row; var Column = RC.Column; var RC = getColumn(old_array,searchString); var Row_old = RC.Row; var Column_old = RC.Column; Logger.log(Row_old+':::'+Column_old+'\n'+Row+':::'+Column); //compare var diff_index =JSON.parse(cache.get('diff_index')); var row_ind =JSON.parse(cache.get('row_ind')); var Roww =JSON.parse(cache.get('Row')); if (diff_index==null){var diff_index = [];} if (row_ind==null){var row_ind = 0;} if (Roww==null){var Roww = Row;} Logger.log(row_ind+'\n'+Roww); for (var i=Roww;i<array.length;i++){ var currTime = (new Date()).getTime(); if(currTime - startTime >= MAX_RUNNING_TIME){ Logger.log((currTime - startTime)/(1000*60)); Logger.log(i+'::'+row_ind); cache.putAll({'diff_index': JSON.stringify(diff_index),'row_ind': JSON.stringify(row_ind),'Row': JSON.stringify(i-1)},21600); ScriptApp.newTrigger('getNewData').timeBased().after(2 * 60 * 1000).create(); return; } else { Logger.log(i); var existe = ArrayLib.indexOf(old_array, Column_old, array[i][Column]); if (existe==-1){ Logger.log(row_ind+'!!!'); diff_index[row_ind]=i; row_ind++; } } } cache.putAll({'diff_index': JSON.stringify(diff_index),'Row': JSON.stringify(Row),'compared': JSON.stringify(1)},21600); } else { Logger.log('file not downloaded yet or already compared'); } } function getColumn(array,searchString){ for (var i = 0; i < array.length; i++) { for (var j = 0; j < array[i].length; j++) { if (array[i][j] == searchString) { var Row = i+1; var Column = j; break; } } if (Row != undefined){ break; } } return {Row: Row, Column: Column}; }
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter