0

I have a google sheet with a column of dates, and I cant display or set the value to a cell if the values on my column reaches 4 times (e.g. July 1, 2019)

      Date Column
Row1: July 1, 2019
Row2: July 2, 2019
Row3: July 1, 2019
Row4: July 4, 2019
Row5: July 1, 2019
Row6: July 1, 2019
Row7: July 5, 2019

i have tried the code below in google script:

  function countDate(){

    var ss = SpreadsheetApp.openByUrl(url);
    var ws = ss.getSheetByName("Test_Data");
    var dateRg = ws.getRange(1, 9, ws.getLastRow(), 1).getValues();

    for (var i =1; i < dateRg.length; i++){
      var dateTest = dateRg[i];

      if (dateTest > 5){
         ws.getRange(2,4).setValue(dateTest);
    }
  }

In the example above, i would like to have a cell in my spreadsheet to have the value "July 1, 2019" since it appears 4 times in the column. Thank you in advance.

GPni
  • 143
  • 1
  • 14
  • there's a similar post https://stackoverflow.com/questions/17372322/gathering-all-the-unique-values-from-one-column-and-outputting-them-in-another-c, maybe it can help – Ed Bangga Jul 17 '19 at 05:23
  • thank you Ed Bangga. But from what I understand in the post, it was looking for a duplicate or not duplicate. Though my data also has duplicates, I need the code to count if the values in the column reaches 4 times. Sorry im only a noob in programmng. thank you. – GPni Jul 17 '19 at 05:37
  • Or to make it simpler I need only the values which have appeared 4x in the column. thank you – GPni Jul 17 '19 at 05:44

1 Answers1

1

Here's a possible approach. Count occurrence of each date, store the count in an object and then find the date that has max occurrences.

function countDate(){  
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Test_Data");
  var dateRg = ws.getRange(1, 1, ws.getLastRow(), 1).getDisplayValues();
  var data = {}
  for (var i =0; i < dateRg.length; i++){
    data[dateRg[i]] = data[dateRg[i]] || 0;
    data[dateRg[i]]++;  
  }
  var max = Object.keys(data).reduce(function(a, b){ return data[a] > data[b] ? a : b });  
  ws.getRange(2,4).setValue(max);
}
Amit Agarwal
  • 10,910
  • 1
  • 32
  • 43
  • Thank you so much. I have additional question. What if I have multiple dates that reaches 4 times? On your code you only displayed the maximum occurrence. Is it possible to display or set the value to all those dates? thank you very much in advance – GPni Jul 17 '19 at 06:59
  • Yes, that is possible. You'll have to modify the code to determine multiple entries that have max occurrences. – Amit Agarwal Jul 17 '19 at 08:04
  • Do you have a sample code? sorry I a newbie in programming. I've been trying to figure out how to modify your code but I'm unsuccessful. Thank you. – GPni Jul 17 '19 at 08:22
  • I also tried using the answer in this link but also unsuccessful: https://stackoverflow.com/questions/48927567/google-apps-script-count-if – GPni Jul 17 '19 at 08:31
  • Cant i have another solution? Pls help. Badly needed.. Thank you – GPni Jul 18 '19 at 12:48