Searching multiple folders for forms by Mimetype Google Scripts

Question

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);
  }

} }"

Answer 1

I have two workarounds in mind.

1. Check the file name using Spreadsheet.getName().

• If your forms has a specified file name format you can verify if the current sheet is a form that you expected based on its file name.

2. Check if sheet "Form" exist when you use Spreadsheet.getSheetByName(). It should return a null if the sheet was not found.

---

var sheet = tickets.getSheetByName("Form");
if (sheet) {
   //Do something
}

You can also use Sheet.getFormUrl() to verify if the current sheet is linked to a form. It should return a null if it doesn't have an associated form