Sometimes my script fails until I open a specific tab

Question

I have a Google Sheets tab dedicated to pulling values from many other tabs. There is a appscript function that takes those values and pushes them to a connected Google Slides deck.

The problem is that sometimes when I run the function, it says "Exception: The match text should not be empty." All i have to do is navigate to that specific tab and to run it again. I do not change anything, but it works.

It is meant to be user friendly and trying to explain that 'all you have to do is navigate to this tab and run it again' is not ideal. Does anyone have an idea of why this behavior happens and how to solve for it? Thank you!

Just refreshing the spreadsheet does not solve it. You have to navigate to that tab before running it again.

Here is the code that performs the 'find and replace' on the google slides. The code works.

 var Presentation = SlidesApp.openById(Presentation_ID) ;
  var values = 
 SpreadsheetApp.getActive().getDataRange().getValues();
  values.forEach(function(row){
    var templateVariable= row[4]
    var templateValue= row[3];
    Presentation.replaceAllText(templateVariable,templateValue) ;
  });

Answer 1

Consider your code line:

var values = SpreadsheetApp.getActive().getDataRange().getValues();

The result depends on the active tab, because you don't specify it explicitly. For example, if the active tab is empty, then values will be [[]]. Not good!

To avoid such unpredictivity, let's change the code line to:

var values = SpreadsheetApp.getActive().getSheets()[0].getDataRange().getValues();

This variant works directly for the first tab (sheet index=0). And more better, use a sheet name instead:

var values = SpreadsheetApp.getActive().getSheetByName("Sheet1").getDataRange().getValues();

If you are not sure, please, use

Logger.log(values);

additionally to check data before .replaceAllText() method.