1

I have a dropdown list with month and year in cell A4 (January 2022, February 2022,...) The script reads it correctly as a string but doesn't do the actual split. I tried to read getValue or getDisplayValue().

It returns a string but doens't want to split it.

function splitMonthYear() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Filter Dates");
  
  let dateFilter = sheet.getRange("A4").getValue()
   
  Logger.log("dateFilter : "   + dateFilter + " typeof : " + typeof(dateFilter)  + " - " + dateFilter.length)
  
  String(dateFilter).split(" ")
  Logger.log("dateFilter 2 : "   + dateFilter + " typeof : " + typeof(dateFilter)  + " - " + dateFilter.length)
  Logger.log("dateFilter[0]: "   + dateFilter[0] + " typeof : " + typeof(dateFilter[0])  )
  
  let monthFilter = dateFilter[0];
  let yearFilter = dateFilter[1] 
  Logger.log("monthFilter : "   + monthFilter + " - yearFilter : " + yearFilter)
}

link to spreadsheet : https://docs.google.com/spreadsheets/d/1cE4FRKeVb8EMqm4q9brpjTcLPpZBH4qXud99lto--WQ/edit#gid=1192683053

Tanaike
  • 181,128
  • 11
  • 97
  • 165
Michel
  • 65
  • 6

2 Answers2

1
function splitMonthYear() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet0");
  let dv = sheet.getRange("A4").getDisplayValue().split(",");
  dv.pop();
  let a = dv.map(e => e.trim())
  Logger.log(JSON.stringify(a));
}

Execution log
5:21:56 PM  Notice  Execution started
5:21:56 PM  Info    ["January 2022","February 2022"]
5:21:57 PM  Notice  Execution completed
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • sorry, I probably stated the problem imprecisely ; the goal was to split the month and year from the selected value in the dropdown list. – Michel Feb 20 '22 at 08:10
1

Modification points:

  • When I saw your script, by String(dateFilter).split(" "), dateFilter is not changed. Please assign the result of it to a variable.

  • When I saw your script, situation, and your sample Spreadsheet, I thought that your issue might be related to your question. Ref Namely, your single space might be A0 instead of 20.

I thought that the above 2 points might be the reason for your issue. When these points are reflected in your script, it becomes as follows.

From:

String(dateFilter).split(" ")

To:

dateFilter = String(dateFilter).split(String.fromCharCode(0xA0));
  • If your single space is 20 which is not A0, please modify to dateFilter = String(dateFilter).split(" ");

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks ! That was it again ! I tried to implement your solution, but failed badly. Thanks again for your help ! – Michel Feb 20 '22 at 08:12
  • @Michel Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Feb 20 '22 at 23:58