Here's my code do:
- Replicates a spreadsheet and dump it to a gdrive folder (no. of replications depends on the number of employees on my config file). As of the moment, I have 800+ employees.
- After replication, each spreadsheet in the gdrive folder are published to html. (i used a loop here)
- Get the pubHTML link per spreadsheet and put it in my config file.
I'm getting the exceeded maximum execution time when I'm on the step 3 part which is the to get the pubHTML and put it in my config file.
My code is working as expected. I want to avoid the maximum limit of execution error.
function replicateCards() {
var ss = SpreadsheetApp.openById('configfile--xxxx');
var copyCard = SpreadsheetApp.openById('replicateCard-xxxx');
var getID = DriveApp.getFileById(copyCard.getId())
var card = copyCard.getSheetByName("Card");
var mastersheet = ss.getSheetByName("Mastersheet");
var getLastRow = mastersheet.getLastRow();
var destinationFolder = DriveApp.getFolderById('gdrivefolder-xxxx');
var changeColorToGrayList = card.getRangeList(['C7', 'E7', 'G7', 'I7', 'K7', 'M7', 'O7', 'Q7',
'C9', 'E9', 'G9', 'I9', 'K9', 'M9', 'O9', 'Q9',
'C11', 'E11', 'G11', 'I11', 'K11', 'M11', 'O11', 'Q11']);
var setValueToZero = card.getRangeList(['C8', 'E8', 'G8', 'I8', 'K8', 'M8', 'O8', 'Q8',
'C10', 'E10', 'G10', 'I10', 'K10', 'M10', 'O10', 'Q10',
'C12', 'E12', 'G12', 'I12', 'K12', 'M12', 'O12', 'Q12']);
for (i = 1; i < getLastRow; i++) {
var badgeStatus = mastersheet.getRange(i + 1, 5).getValue();
if (badgeStatus == "") {
var employeeNumber = mastersheet.getRange(i + 1, 1).getValue();
var employeeName = mastersheet.getRange(i + 1, 2).getValue();
copyCard.getRange("H3").setValue(employeeNumber);
copyCard.getRange("C3").setValue(employeeName);
SpreadsheetApp.flush();
if(mastersheet.getRange(1 + i, 5).getValue() != "completed"){
getID.makeCopy(employeeNumber, destinationFolder);
mastersheet.getRange(1 + i, 5).setValue("completed");
}
// Logger.log(i);
SpreadsheetApp.flush();
}
}
var files = DriveApp.getFolderById(SpreadsheetApp.openById("configFile-xxxx").getSheetByName("Config Sheet").getRange("B1").getValue()).getFiles();
//var fileIter = 1;
var employeeNumbers = mastersheet.getRange("A2:A").getValues();
var employeeNumbersTrunc = []
for(var i = 0; i < employeeNumbers.length; i++){
if(employeeNumbers[i][0] != "")
employeeNumbersTrunc.push("" + employeeNumbers[i][0]);
}
Logger.log(employeeNumbersTrunc);
while (files.hasNext()) {
var file = files.next();
/*var Found = false;
for (var j = 0; j < ; fileIter++) {
if (employeeNumber2[j][0] == file.getName()) {
Found = true;
}
}//*/
if (employeeNumbersTrunc.indexOf(file.getName())==-1) {
continue;
}else if(mastersheet.getRange(2 + (employeeNumbersTrunc.indexOf(file.getName())), 9).getValue() != ""){
continue;
}
try {
var fileId = file.getId();
var fileName = file.getName();
var revisions = Drive.Revisions.list(fileId);
var lastRevisionId = revisions.items[revisions.items.length - 1].id;
// get the resource and set the publish parameters
var resource = Drive.Revisions.get(fileId, lastRevisionId);
// Logger.log(resource);
resource.published = true;
resource.publishAuto = true;
resource.publishedOutsideDomain = true;
// publish to the web
Drive.Revisions.update(resource, fileId, lastRevisionId);
SpreadsheetApp.flush();
var openByID = SpreadsheetApp.openById(fileId);
SpreadsheetApp.flush();
var googleDriveSheet = openByID.getUrl().replace("edit", "pubhtml"); // or replace("edit", "pub");
SpreadsheetApp.flush();
Logger.log(file.getName());
Logger.log(employeeNumbersTrunc.indexOf(file.getName()));
mastersheet.getRange(2 + (employeeNumbersTrunc.indexOf(file.getName())), 9).setValue(googleDriveSheet);
SpreadsheetApp.flush();
} catch (err) {
Logger.log(err);
}
}
}
Run the code one time only without the exceeded maximum execution time error. Any suggestions?
