I have a Google Apps Script function which I want to use to do this: extract some data (a date) from a raw text in a spreadsheet cell. When I use a javascript IDE, the code works fine. But when I try it on GAS, it doesn't work.

This is the code:

function findDate (text){
text1 = text.split(".Date");
Logger.log("text1", text1);
//console.log("text1= ", text1);
date = 'no date informed';

for (var i=0; i<text1.length; i++) {
     text2 = text1[i].split(" ");
     Logger.log("text2", text2);
//     console.log("text2= ", text2);
  //   console.log("text2[1]= ", text2[1]);
     if (text2[1] === 'mon:'){
         date = text2[2];
       Logger.log("text2[2]", text2[2]);
//         console.log("text2[2]= ", text2[2]);

     }
  }
  return date;
}

The string which is in the spreadsheet cell is not exactly this, it's writen in another language, but it's similar to this (broken text, but without the space between lines):

special term: 19 years, 6 months and 0 days.

mom term: 8 years, 8 months and 0 days.

Date special: 23/11/1998

Date mon: 09/11/2012

When I get the logs, I got the message Logging output too large. Truncating output. and this text:

[text1, [[special term: 19 years, 6 months and 0 days. mom term: 8 years, 8 months and 0 days. Date special: 23/11/1998 Date mom: 09/11/2012]]]

THANKS in advance for any help!

I have a Google Apps Script function which I want to use to do this: extract some data (a date) from a raw text in a spreadsheet cell. When I use a javascript IDE, the code works fine. But when I try it on GAS, it doesn't work.

This is the code:

function findDate (text){
text1 = text.split(".Date");
Logger.log("text1", text1);
//console.log("text1= ", text1);
date = 'no date informed';

for (var i=0; i<text1.length; i++) {
     text2 = text1[i].split(" ");
     Logger.log("text2", text2);
//     console.log("text2= ", text2);
  //   console.log("text2[1]= ", text2[1]);
     if (text2[1] === 'mon:'){
         date = text2[2];
       Logger.log("text2[2]", text2[2]);
//         console.log("text2[2]= ", text2[2]);

     }
  }
  return date;
}

The string which is in the spreadsheet cell is not exactly this, it's writen in another language, but it's similar to this (broken text, but without the space between lines):

special term: 19 years, 6 months and 0 days.

mom term: 8 years, 8 months and 0 days.

Date special: 23/11/1998

Date mon: 09/11/2012

When I get the logs, I got the message Logging output too large. Truncating output. and this text:

[text1, [[special term: 19 years, 6 months and 0 days. mom term: 8 years, 8 months and 0 days. Date special: 23/11/1998 Date mom: 09/11/2012]]]

THANKS in advance for any help!

• javascript
• google-apps-script
• google-sheets

2 Answers 2

You can add a sheet to your spreadsheet called Code and add a range to it for "Debug Watch" named DebugWatchWindow where you can add watches of much longer length, up to 50,000 characters. Clear the debug "window" when you start your script, and add watches wherever you want in your code.

The My Executions window is more handy, but this method is good for long log text:

Notes:

• Change the reference to your code sheet and these Debug functions as necessary for your app.
• DebugWatchWindow is where "fred" is. It's the top left cell of the "Debug Window" data.
• don't put anything of importance below that. It will get trashed.

debug.gs

  function getWatchWindow() {
    if(!this.WatchWindow) 
            this.WatchWindow = App.Sheets.codeSheet.getRange('DebugWatchWindow').offset(0, 0, 20, 2); // fixed size
    return this.WatchWindow;
  }

  function clearWatch() {
    this.getWatchWindow().offset(0,0, App.Sheets.codeSheet.getLastRow(), 2).clearContent();
  }

  function addWatch(expression, value) {
    var rows = App.Sheets.getLastUsedSize(this.getWatchWindow()).rows;
    this.getWatchWindow().offset(rows, 0, 1, 1).setValue(expression);
    this.getWatchWindow().offset(rows, 1, 1, 1).setValue(value);
  }

sheets.gs

  function getLastUsedSize(range) {
    var values = range.getValues();
    return values
    .reduce(function(size, rowValues, index, values) {
      var lastUsedColumn = rowValues
      .map(function(cellValue, index) {
        if(cellValue != '')
          return index;
        else
          return -1;
      })
      .reduce(function(acc,curr) {
        acc = Math.max(acc,curr);
        return acc;
      }, -1);
      if(lastUsedColumn > -1) {
        size.rows = index + 1;
        size.columns = Math.max(size.columns, lastUsedColumn + 1);
      }
      return size;
    }, {columns: 0, rows: 0});
  }

tests.gs

function testScratch() {
  console.log('testScratch');
  App.Debug.clearWatch();
  App.Debug.addWatch('fred', 14);
  App.Debug.addWatch('jimmard', App.Dates.now());
  sdfsdd();
}

Logger has a limited size of what it can display in the popup window and that's why it truncates the results you see.

If you remove the first occurrence in your code, which shows the initial array data, you will get the entire result. Displaying the entire array apparently exceeds the length limit.

Without this change:

using the data as above and the code as below

function myFunction() {
  var text = SpreadsheetApp.getActive().getRange("A1").getValue();
  Logger.log('text = '+text);
  Logger.log('function result = '+findDate(text));

}


function findDate (text){
  var text1 = text.split("\n"+"Date");
  Logger.log("text1 = "+ text1);
  var date = 'no date informed';
  
  for (var i=0; i<text1.length; i++) {
    var text2 = text1[i].split(" ");
    Logger.log("text2 = "+text2);
    if (text2[1] === 'mon:'){
      date = text2[2];
      Logger.log("text2[2] = "+ text2[2]);
    }
  }
  return date;
}

I get the following result :