Trigger: ERROR The starting row of the range is too small

Question

My Trigger Log shows the above error whenever I try to use the Timed Trigger. I know these functions work as they can be run directly from the Script Editor. I know the scope and environment are different for functions that are run on the server (a la trigger) as opposed to the console and I’ve tried to accommodate that in the script - but clearly failed. Can anyone point me in the right direction please.

The process Every 10 seconds the script is supposed to pick up a 10 by 3 range, Copy/Paste VALUES in place then copy the formula in the bottom row of the range down through the next 10 rows. It then resets the one shot timer. It will do this until a maximum number of rows is reached or the loop has run n times. This is clearly the minimised test case. The actual live dataset needs to traverse 200,000+ rows across a number of sheets with a significantly more complex query formula.

Running the formula on the real dataset generates an ‘Aw snap!’ after 36 hours runtime. This script is supposed to break that formula method into bite sized chunks. Run time is not an issue, just the integrity of the result.

I’m trying to avoid having to rewrite the query formula as a script but if you guys say that’s the only way then so-be-it. This trigger version of the script has been a valuable learning curve on the GAS trigger App so it’s not been wasted time - but this last bit has exhausted all my attempts at a workaround.

The link to the test sheet is here

Please make a copy of the sheet as each instance will need to be authorised. Please replace the SPREADSHEET_ID in the script.

[Edit as requested 21/06/19] Here is a screen grab of the Execution Log error.

[19-06-21 02:59:17:590 PDT] Starting execution
[19-06-21 02:59:17:600 PDT] PropertiesService.getScriptProperties() [0 seconds]
[19-06-21 02:59:17:606 PDT] Properties.getProperty([TIMECOUNT]) [0.005 seconds]
[19-06-21 02:59:17:718 PDT] SpreadsheetApp.openById([xxxxxxxxxxxxxxxxxxxxxxxxxxx]) [0.109 seconds]
[19-06-21 02:59:17:719 PDT] Spreadsheet.getSheetByName([ORG Data]) [0 seconds]
[19-06-21 02:59:17:720 PDT] Sheet.getCurrentCell() [0 seconds]
[19-06-21 02:59:17:720 PDT] Range.getRow() [0 seconds]
[19-06-21 02:59:17:721 PDT] Sheet.getCurrentCell() [0 seconds]
[19-06-21 02:59:17:721 PDT] Range.offset([-10, 0, 10, 3]) [0 seconds]
[19-06-21 02:59:17:729 PDT] Execution failed: The starting row of the range is too small. (line 51, file "Code") [0.124 seconds total runtime]

function onOpen() {             
  SpreadsheetApp.getUi()                
    .createMenu('My Tools')
    .addItem('Start Time Trigger', 'TriggerBlocks')
    .addItem('Stop Time Trigger', 'stopTrigger')
    .addToUi(); 
  PropertiesService.getScriptProperties().setProperties({'TIMECOUNT': '0', });
}

  var MAX_TCOUNT = 3;

function TriggerBlocks(){
  ScriptApp.newTrigger("pasteThenCopy")
    .timeBased()
    .after(10*1000)    // 10 seconds
    .create(); 
}

function pasteThenCopy(){
  var timecount = PropertiesService.getScriptProperties().getProperty('TIMECOUNT');
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(SHEET_NAME);
  var ssrow = sheet.getCurrentCell().getRow();
  timecount++;     // coerces string to number
  if( ssrow < 25 && timecount < MAX_TCOUNT) {
    PasteCalcs1000rows(sheet,CopyFormula1000rows);
    PropertiesService.getScriptProperties().setProperty('TIMECOUNT',timecount);
    TriggerBlocks();
    }
};

function PasteCalcs1000rows(sheet,callback) {
  if(sheet == undefined){sheet=SpreadsheetApp.getActiveSheet();};
    sheet.getCurrentCell().offset(-10, 0, 10, 3).activate();
    sheet.getActiveRange().copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sheet.getCurrentCell().offset(10, 0).activate();
  callback(sheet);
};

function CopyFormula1000rows(sheet) {
  if(sheet == undefined){sheet=SpreadsheetApp.getActiveSheet();};
  sheet.getCurrentCell().offset(0, 0, 11, 1).activate();
  sheet.getCurrentCell().copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  sheet.getCurrentCell().offset(10, 0).activate();
}

Please edit your question to include the Execution transcript for a failed processing run. — Tedinoz
@Tedinoz Thanks for looking at the script. I've added a screen grab of the Execution Log error. — DeeKay789
Looks like your offset(-10, 0, 10, 3).activate() is trying to activate a cell that doesn't exist. Any row number below "1" will throw this error. — ross
Beat me to it @ross. The error is because the current cell is on a row in the first 9 rows. Line 51 creates an offset of -10 rows from the current cell. but this generates a value <0; hence the error. — Tedinoz
@DeeKay789 Doing the manual changes to the sheet and then opening in the code will not grab the currentCell from what you manually did. you need to set the H12 cell as active at the begining and then it will behave as intended. your code now is setting the active cell as A1 (when it grabs the sheet) and that's why it behaves in the way that ross and Tedinoz suggested. TLDR; try sheet.getRange("H12".activate()); before calling the offset — AMolina

AMolina AMolina · Accepted Answer · 2019-06-25T07:15:56

Posting answer from comment for documentation purposes:

Doing the manual changes to the sheet and then opening in the code will not grab the currentCell from what you manually did. you need to set the H12 cell as active at the begining and then it will behave as intended. Your code now is setting the active cell as A1 (when it grabs the sheet) and that's why it behaves in the way that ross and Tedinoz suggested.

TLDR; try sheet.getRange("H12".activate()); before calling the offset

Trigger: ERROR The starting row of the range is too small

1 Answers