Why Is The Substring Function Not Working?

Question

I am getting an error message for a program that normally works, code below:

  var sheet = SpreadsheetApp.openByUrl('url.com');
  var ss = SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Sheet1'));
  
  var date = new Date();
  Logger.log(date);                                 //Sat Aug 22 17:28:54 GMT+01:00 2020
  var day = date.getDate(); 
  Logger.log(day);                                  //22
  
  for (var i = 2; i < 1400; i++) {
    var gamedandt = ss.getRange(i, 1).getValue();
    Logger.log(gamedandt);                          //Sat Aug 22 14:00:00 GMT+01:00 2020
    /*Line 47*/ var gameday = gamedandt.substring(9, 2);
  }

}

The error message I receive says:

TypeError: gamedandt.substring is not a function (line 47, file "myFile")

I believe the problem to be the data I want substringed - it is a date. The script worked fine in the past but the data would simply have the date and time (eg. '22 14:00') rather than the now full date which includes month, year, weekday, etc.

TheMaster · Answer 1 · 2020-08-22T17:54:54.073

1

Date does not have a substring method. It is possible to convert Date to a string using

one of the Date instance methods: .toString(),.toLocaleString(),.toLocaleDateString()
or directly get the displayed string using getDisplayValue() instead of getValue()
To getDate() from Date,
```
var gameday = gamedandt.getDate();//22
```

edited Aug 22 '20 at 17:54

answered Aug 22 '20 at 17:17

TheMaster

45,448
6
62
85

So how would I pull the '22', which is the day, from the 'gamedandt' variable (data stored in this is 'Sat Aug 22 14:00:00 GMT+01:00 2020')? – Michael Aug 22 '20 at 17:25
1

@Michael See the first link. Let me know which of those methods seem more likely to pull the day of the month – TheMaster Aug 22 '20 at 17:28
@Michael Huh. Wasn't my second link getDisplayValue()? – TheMaster Aug 22 '20 at 20:05

score 1 · Accepted Answer · answered Aug 22 '20 at 17:51

1

Instead of getValue() / getValues() you could use getDisplayValue() / getDisplayValues() to get strings instead of (JavaScript data types) Date, Boolean, Number values when the cell contains values of type (Google Sheets / spreadsheets data types) DATE, TIME, DATE TIME, DURATION, NUMBER, BOOLEAN, etc.

Rubén

34,714
9
70
166

1

Words do not describe how I feel after finding this out. I have been using Google Apps Scirpt for years and I am only just finding out about this!? This getDisplayValue() is going right into my toolkit. Grazie mille Rubén, I hope you have a great night tonight - it is deserved. – Michael Aug 22 '20 at 19:16

Why Is The Substring Function Not Working?

2 Answers2