What is it?
The google sheet that the macros are on acts as a sort of "back-end" for the records of a behaviour system at a school. I build multiple google sheets that link to this one that pull the data. Each week the behaviour records are put in this main sheet and then formulas total up the rest. The macros are for when a pupil is added, removed or a new one starts to make sure the data stays in line with the pupil.
The Problem
When it is full of data it take too long to perform the operations and can timeout the google sheet (especially on school computers). execution log of move pupil code
Solution?
From the research I have done I could try batching code (don't know how to do them in scripts) but would like to know any ways to change/ rework my code to make it way more efficient. google sheet with scripts as macros
Code
Add Pupil Code
function insertRow() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var row = SpreadsheetApp.getActiveRange().getRow();
for (var i = 0; i < sheets.length; i++) {
sheets[i].insertRowBefore(row);
}
}
Remove Pupil Code
function deleteRow() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var row = SpreadsheetApp.getActiveRange().getRow();
for (var i = 0; i < sheets.length; i++) {
sheets[i].deleteRow(row);
}
}
Move Pupil Code
function moveRow() {
var ui = SpreadsheetApp.getUi();
var formResponse = ui.prompt('Move Pupil Row', 'Enter the pupils new form', ui.ButtonSet.OK_CANCEL);
var currentResponse = ui.prompt('Move Pupil Row', 'Enter the current row number for this pupil', ui.ButtonSet.OK_CANCEL);
var newResponse = ui.prompt('Move Pupil Row', 'Enter the new row number for this pupil', ui.ButtonSet.OK_CANCEL);
if (newResponse.getSelectedButton() == ui.Button.OK) {
Logger.log('Collected New Form', formResponse.getResponseText());
Logger.log('Collected Current Row', currentResponse.getResponseText());
Logger.log('Collected New Row', newResponse.getResponseText());
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var row = currentResponse.getResponseText()
var newForm = formResponse.getResponseText()
var pupilData = SpreadsheetApp.getActive().getSheetByName('PupilData');
var formCell = pupilData.getRange("A"+row);
formCell.setValue(newForm);
var newRow = newResponse.getResponseText()
var newRowRange = "A"+newRow+":AP"+newRow
if (row < newRow) {
var rowRange = "A"+row+":AP"+row
} else {
row = +row
row = row + 1
var rowRange = "A"+row+":AP"+row
}
for (var i = 0; i < sheets.length; i++) {
sheets[i].insertRowBefore(newRow);
}
for (var i = 0; i < sheets.length; i++) {
sheets[i].getRange(rowRange).moveTo(sheets[i].getRange(newRowRange));
sheets[i].deleteRow(row);
}
var halfTerm1 = SpreadsheetApp.getActive().getSheetByName('HT1');
var halfTerm2 = SpreadsheetApp.getActive().getSheetByName('HT2');
var halfTerm3 = SpreadsheetApp.getActive().getSheetByName('HT3');
var halfTerm4 = SpreadsheetApp.getActive().getSheetByName('HT4');
var halfTerm5 = SpreadsheetApp.getActive().getSheetByName('HT5');
var halfTerm6 = SpreadsheetApp.getActive().getSheetByName('HT6');
var year = SpreadsheetApp.getActive().getSheetByName('Year');
halfTerm1.getRange('C5:AP').clearContent();
halfTerm2.getRange('C5:AP').clearContent();
halfTerm3.getRange('C5:AP').clearContent();
halfTerm4.getRange('C5:AP').clearContent();
halfTerm5.getRange('C5:AP').clearContent();
halfTerm6.getRange('C5:AP').clearContent();
year.getRange('C5:AP').clearContent();
} else if (newResponse.getSelectedButton() == ui.Button.CANCEL) {
Logger.log('The user canceled the dialog.');
} else {
Logger.log('The user closed the dialog.');
}
}