CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsvariable-declaration
Dan Howard
Dan Howard

Reputation: 316

ReferenceError on Array item

This should be a really simple script that changes the title of a column between 3 columns. I got it to work once, but now it's coming up with an error I don't understand:

ReferenceError: "Today" is not defined. (line 10, file "Code")

Code is as follows:

function Progressions() {
  var workoutSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Workouts");

  var Increment = workoutSheet.getRange(2, 1, 1, 3).getValues();
  var newRoutine =[[]]

  if (Increment == [[Today,,]]){ 
    var newRoutine = [[ ,Today, ]]
    Increment.setValues(newRoutine);
  } else {
    if (Increment == [[,Today,]]){ 
      var newToday = [[,,Today]]
      Increment.setValues(newRoutine);
    } else {
      if (Increment == [[,,Today]]){ 
        var newToday = [[Today,,]]
        Increment.setValues(newRoutine);
      }
    }
  }

Obviously Today is not defined as that's just the content of the array. I think this script should just change the content of the newToday array and then enter it into the todayIncrement range.

Any ideas how I can remove this error and achieve my goal?

Upvotes: 0

Views: 138

Answers (1)

James D
James D

Reputation: 3142

There are several reasons why your code isn't working such as "Today" but also in your arrays with you call .getValues(); the returned array will be something like [["","Today",""]]

You can't use Increment.setValues(------); as increment is an array an not a range object.

The if statement should be constructed 

if(-----){

} else if (-----) {

} else {

}

This code does as you require:

function Progressions() {
    var workoutSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Workouts");
    var range = workoutSheet.getRange(2, 1, 1, 3);
    var increment = range.getValues();

    var newRoutine;
    var testValue = "Today";

    if (increment[0][0] == testValue) {
        newRoutine = [["", "Today", ""]];
    } else if (increment[0][1] == testValue) {
        newRoutine = [["", "", "Today"]];
    } else if (increment[0][2] == testValue) {
        newRoutine = [["Today", "", ""]];
    }
    range.setValues(newRoutine);
}

An alternate implementation of the if-else chain that is easier to extend (e.g. if you had 365 different workouts as columns, the above method needs 365 if-else checks), at the cost of needing more JavaScript knowledge to understand how it works:

function Progressions() {
  const workoutSheet = SpreadsheetApp.getActive().getSheetByName("Workouts"),
      // 1 column per workout (here we have 3).
      headerRange = workoutSheet.getRange(2, 1, 1, 3),
      // Assume headers are in the range's first row.
      headers = headerRange.getValues()[0],
      newRoutine = [];

  // Use Array#indexOf to do all the nasty "if-else if" checking.
  const searchValue = "Today";
  const columnIndex = headers.indexOf(searchValue);
  if (columnIndex === -1)
    throw new Error("'" + searchValue + "' not found in headers ('" + headerRange.getA1Notation() + "').");

  // Set default header values (no Array#fill in Apps Script yet).
  headers.forEach(function (s) { newRoutine.push(""); });

  // Use addition and the modulus operator to increment and wrap.
  const newIndex = (columnIndex + 1) % newRoutine.length;
  newRoutine[newIndex] = searchValue;

  // Write the new header values.
  headerRange.setValues( [newRoutine] );
}

Upvotes: 1

Related Questions