One of my scripts being used for a fantasy hockey-like game is failing to execute as of last week after having worked without issue for the past 54 games of the NHL season. This was my first go at scripting, so there is likely a simple error here, but for the life of me I can't seem to sort out why this is timing out now whereas it was taking mere seconds to execute previously.
The script uses an edit trigger to pull the game number from a sheet called 'Scoring' and then adds a total of 20 rows to the end of the 'History' sheet with that game number value set in the first column, then copies the box score to those 20 rows, and finally a separate 'Scorecard' sheet uses the 'History' data to figure each participants score.
Suddenly, the script lags heavily when adding the new last rows and setting the first column value to the 'gamenum'. The script seems to lag at different rates, but now cannot seem to get past creating and setting the value of between 3 and 9 new last rows before timing out.
Any advice will be greatly appreciated.
Thank you, M
function statHistory(){
var sourcesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Scoring');
var gamerange = sourcesheet.getRange('B1');
var gamenum = gamerange.getValues();
var statrange = sourcesheet.getRange('B11:W28');
var stats = statrange.getValues();
var teamgoalierange = sourcesheet.getRange ('B31:W32')
var teamgoaliestats =teamgoalierange.getValues();
var targetsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('History');
for (var gamehist = 0; gamehist <=19;gamehist = gamehist + 1){
targetsheet.getRange(targetsheet.getLastRow()+1,1).setValues(gamenum)
}
targetsheet.getRange(targetsheet.getLastRow()-19,2,18,22).setValues(stats)
targetsheet.getRange(targetsheet.getLastRow()-1,2,2,22).setValues(teamgoaliestats)Try it this way:
function statHistory() {
const ss = SpreadsheetApp.getActive();
const shs = ss.getSheetByName('Scoring');
const gamerange = shs.getRange('B1');
const gamenum = gamerange.getValue();
const statrange = shs.getRange('B11:W28');
const stats = statrange.getValues();
const teamgoalierange = shs.getRange('B31:W32')
const tgstats = teamgoalierange.getValues();
const tsh = ss.getSheetByName('History');
let a = []
for (let i = 0; i < 20; i++{ a.push([gamenum]) }
tsh.getRange(tsh.getLastRow() + 1, 1,a.length,a[0].length).setValues(a);
tsh.getRange(tsh.getLastRow() - 19, 2, stat.length, stats[0].length).setValues(stats);
tsh.getRange(tsh.getLastRow() - 1, 2, tgstats.length, tgstats[0].length).setValues(tgstats);