Due to the beginning of the SSAF* project being very frustrating and the Google Sheets tutorials being unforgivably vague, I've decided to make a more in-depth post about a small script I wrote for my new job at Aqua Tots.
Warning: Google sheets uses bound scripts so you have to open the script from Google sheets by selecting 'Tools > Script editor'. Without doing that you won't be able see the results from your script.
The first thing we need to do is to confirm which sheet we're working on. While it's always going to be the one open, we need to confirm the link to it using a prompt. I used the following code:Warning: Google sheets uses bound scripts so you have to open the script from Google sheets by selecting 'Tools > Script editor'. Without doing that you won't be able see the results from your script.
function showPrompt() {
Logger.log('(SSAF): Requesting spreadsheet link');
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.prompt(
'Please enter the link to the schedule you would like sorted',
'(Google Sheets links ONLY)',
ui.ButtonSet.OK_CANCEL);
// Process the user's response.
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK && text != "") {
// User clicked "OK".
return text;
} else {
showPrompt();
}
}
Which resulted in this:
Our schedules came in the following format:
But they needed to be converted into this format:
The Task:
The schedules needed a lot of work. The had to be organized by Staff /Time, the time column had to be converted to Standard format and moved to the first column and after the excess information was deleted, they needed to be indented by comparing class and staff.
The Function:
This is the function I used to sort my data and organize it as necessary. Hopefully this will help those of you struggling to integrate Google Sheets Scripts into your work.
function sortSchedule() {
var link = showPrompt();
var spreadsheet = SpreadsheetApp.openByUrl(link);
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
var tableRange = "A2:I200"; // What to sort
var range = sheet.getRange(tableRange);
var lastCol = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
Logger.log('(SSAF): Sorting ' + String(tableRange) + ' by name and time..');
var r = sheet.getRange(2, 1, lastRow - 1, lastCol); // assumes headers in row 1
r.sort([{ column: 5, ascending: true }, { column: 3, ascending: true}]);
Logger.log('(SSAF): Sorting Complete!');
Logger.log('(SSAF): Deleting extra data...');
sheet.deleteColumn(9);
sheet.deleteColumn(8);
sheet.deleteColumn(7);
sheet.deleteColumn(4);
Logger.log('(SSAF): House keeping...');
for (i = 2; i < 1000; i++) {
var range = sheet.getRange(i,3);
var range2 = sheet.getRange(i+1,3);
var range3 = sheet.getRange(i,4);
var range4 = sheet.getRange(i+1,4);
var data = range.getValue();
var data2 = range2.getValue();
var data3 = range3.getValue();
var data4 = range4.getValue();
if (data.localeCompare(data2) != 0 || data3.localeCompare(data4) != 0) {
sheet.insertRowAfter(i);
Logger.log('(SSAF): Adding indentation in row ' + i);
i++; // skips newly created row
} else if (data.localeCompare("") == 0) { //empty field
Logger.log('(SSAF): Indentation loop broken at row ' + i);
break;
}
}
Logger.log('(SSAF): Moving time to Column One...');
sheet.insertColumns(1, 1);
var valuestocopy = sheet.getRange("E1:E1000");
valuestocopy.copyValuesToRange(sheet, 1, 1, 1, 1000);
sheet.deleteColumn(5); //deleting duplicate column
Logger.log('(SSAF): Converting to standard time...'); //Column 5
for (i = 2; i < 1000; i++) {
var range = sheet.getRange(i,1).getValue();
if (range == "" && sheet.getRange(i+1,1).getValue() == "") {
Logger.log('(SSAF): It broke at ' + i);
break;
}
var h = range.substr(0,2);
var mm = range.substr(3);
//var m = +mm;
Logger.log('(SSAF): time broken down ' + range + ' to ' + h + ':' + mm);
if (h > 12) {
h=h-12;
var val = h +':' + mm;
sheet.getRange(i,1).setValue(val);
Logger.log('(SSAF): Converting ' + range + ' to ' + r); //Column 5
sheet.getRange(i,1).setNumberFormat("HH:mm");
}
}
Logger.log('(SSAF): Schedule Complete.');
}
