I had a system where I kept a series of Job Tickets in a folder. I then had two scripts that would search within that folder, look at each of the job tickets, pull certain info, and put it into either a timesheet calculator or a list of open jobs.
I'm merging two departments together and want to use the same system. To do so, I needed to organize my jobs not within one large folder, but as subfolders. That's fine and works. However, my code searches for spreadsheets by MimeType in the folder. When they add another Google Sheet to the folder, my code trips up because the new Sheet is the same MimeType, but doesn't have the same format to search in.
Is there a way around this without trying to change user behavior? All job tickets have a similar format: Sitename, 3-4 word description, ticket number. I couldn't think of a way to put that to searchable terms, which is why I went for MimeType.
Here's what has worked to date. It is for the timesheet, but the other script works in the same fashion.
function OpenJobs() {
var ss = SpreadsheetApp.getActive()
var parentFolder = DriveApp.getFolderById("Open Folder");
var childFolders = parentFolder.getFolders();
while(childFolders.hasNext()) {
var child = childFolders.next();
Logger.log(child.getName());
var sheet = ss.getSheetByName("Scripts");
var files = child.getFilesByType(MimeType.GOOGLE_SHEETS)
var array=[];
while (files.hasNext()) {
var file = files.next()
var filelog = file.getUrl()
var tickets = SpreadsheetApp.openById(file.getId())
var dataTickets = tickets.getSheetByName("Form").getDataRange().getValues();
var ticketno = dataTickets[0][1];
var jobname = dataTickets[0][3];
var worker1 = dataTickets[26][0];
var workhours1 = dataTickets[26][2];
var workdate1 = dataTickets[26][4];
var worker2 = dataTickets[27][0];
var workhours2 = dataTickets[27][2];
var workdate2 = dataTickets[27][4];
//and so on for 10 lines
array.push([ticketno,jobname,worker1,workhours1,workdate1]);
array.push([ticketno,jobname,worker2,workhours2,workdate2]);
//and so on for 10 lines
}
if(array[0]){
sheet.getRange(sheet.getLastRow()+1,1,array.length,5).setValues(array);
}
}
}"
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…