3
\$\begingroup\$

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.');
}
}
πάντα ῥεῖ
5,1524 gold badges23 silver badges32 bronze badges
asked Jul 25, 2019 at 18:14
\$\endgroup\$

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.