TypeError: Cannot read property 'getSheetByName' of null

Question

Looking for some help with a code in script app. I'm using the below code to get data from one sheet and then put it in the last row of another sheet. Both sheets are in the same workbook. I'm taking a monthly snap shot of a budget, so I have a historical view. The code runs when I manually 'run' it, however when I set up an automatic trigger I get the following error message TypeError: Cannot read property 'getSheetByName' of null at recordVariancesHistory(Variances script:3:19)

I have the same code set up in another spreadsheet and the trigger takes a snap shot each month without issue. I can't figure out what's different in this spreadsheet.

function recordVariancesHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('ForMaster');
  var sheet2 = ss.getSheetByName('Variances History');
  var data = sheet1.getRange('z84:Af87').getValues();
  var last_row = sheet2.getLastRow();

  sheet2.getRange(last_row + 1,1,4,7 ).setValues(data);

 }

Any help would be greatly appreciated.

Answer 1

Change getActiveSpreadsheet to openByUrl or openById. If you need dynamical search file, use DriveApp in order to find it.