1

I am trying to create a JavaScript loop for gmail spreadsheets that counts the cells that have the yesterday date and give the sum in a different cell. I know I can do it through excel or in the spreadsheet but I prefer doing it through the script editor (I am newbie). Here is my code:

function job_counter(k) {

  var row = k.range.getRow();
  var col = k.range.getColumn();
  var yesterday_jobs = 0;
  var today = new Date();
  today;
  console.log(today)
  var yesterday = today - 1
  yesterday;
  console.log(yesterday)

  for (var i = 1; i < 25; i++){
    if (col === 3 && row === i && k.source.getActiveSheet().getRange(row,col).getValue() === yesterday) 
    yesterday_jobs === yesterday_jobs+1;
    console.log(yesterday_jobs);
    k.source.getActiveSheet().getRange(16,1).setValue(yesterday_jobs);
   } // check yesterday_jobs                          
}

It does not seem to work plus I get the message

TypeError: Cannot read property "range" from undefined. (line 3, file "Code"

that should not be a problem.

Cooper
  • 59,616
  • 6
  • 23
  • 54
johnjohn1
  • 55
  • 7
  • Please include enough code to duplicate the problem. The function do not have problem by itself. The data you passed to the function is undefined at the moment you called the function. – Addis Dec 01 '19 at 15:59
  • 1
    @Addis It looks that the problem is that the OP is running the function directly from the Google Apps Script editor ( the function parameter k is undefined) – Rubén Dec 01 '19 at 16:01
  • @Rubén what do you mean that the function parameter k is undefined? And yes I am running the function directly from Google Apps script. but it has worked before with different scripts – johnjohn1 Dec 01 '19 at 16:12
  • @Addis what do you mean? – johnjohn1 Dec 01 '19 at 16:26
  • 1
    johnjohn1: I think that you should start by reading https://developers.google.com/apps-script/guides/sheets – Rubén Dec 01 '19 at 16:26
  • @Rubén thanks for the link. But can you recommend a solution from the problem? – johnjohn1 Dec 01 '19 at 16:34
  • 1
    Yes I can but first we should share a basic understanding about how Google Apps Script and Google Sheets work. Have you already read the link in my previous comment? – Rubén Dec 01 '19 at 16:53
  • Is this meant to be an assignment or a comparison `yesterday_jobs === yesterday_jobs+1;` If it's a comparison then why is it not being used. – Cooper Dec 01 '19 at 17:45
  • 1
    This is going to be substantially slower doing it with a script? I would literally have a script insert the formula if you insist... – CodeCamper Dec 01 '19 at 18:14
  • @CodeCamper It never ceases to amaze me how people insist on writing spaghetti code and avoid using formulas at all costs. Here you can literally insert one SUMIF wrapped into an array formula, and you are done. You can even have the formula disappear from the cells by calling getValues() and setValues() in succession – Anton Dementiev Dec 01 '19 at 18:30
  • @johnjohn1 I second Ruben's advise. Please take some time to learn more about JavaScript syntax and get the basics out of the way. – Anton Dementiev Dec 01 '19 at 18:33
  • Two questions/comments:1) The rows "today;" and "yesterday;", are those supposed to be commands? I believe they can be removed. 2) The if statement does not have a pair of curly brackets ("{" and "}"). If leaving out the brackets, then it is only the first row after the if row that is conditional. The following two will always run, regardless of the if statement. – Pierre Sundberg Dec 01 '19 at 20:52

0 Answers0