Javascript in googlespreadsheets

Question

I am trying to create a counter that counts the cells that include in their content the date before today as a date. However the result in cell 16,1 is always zero as it seems that my loop does not work. I know I can do it with a formula in spreadsheets but I want to use javascript. Also I am trying to find out what is wrong in MY code. I have wrirtten the following lines of code:

function job_counter() {


  var yesterday_jobs=0;
  var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var e = new Date(new Date().getFullYear(),new Date().getMonth() , new Date().getDate())
  var yesterday = new Date(new Date().getFullYear(),new Date().getMonth() , new Date().getDate())
  yesterday.setDate(yesterday.getDate() - 1);
  var range_unformated=ss.getRange(2,3,25).getValues()
  var date;
  for (var i=1; i<25; i++){
      date=Date.parse(range_unformated[i])
      Logger.log(date[3])
      if ( date[i] - yesterday.getTime() >= 0 && date[i] != ""  ){
      yesterday_jobs = yesterday_jobs + 1 
    ss.getRange(16,2).setValue(yesterday_jobs)
    }}
    // check yesterday_jobs                       
}

Please add a [mcve] as is suggested in [ask]. – Rubén Dec 05 '19 at 00:02 — Rubén, Dec 05 '19 at 00:02

score 0 · Answer 1 · answered Dec 04 '19 at 18:17

0

The example below shows how to retrieve and log the items names and items numbers.

function logItemstInfo() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
Logger.log('Items name: ' + data[i][0]);
Logger.log('Items number: ' + data[i][1]);
}
}

answered Dec 04 '19 at 18:17

Rahul Kr Daman

387
3
15

I have tried logger.log but I can not find the problem. My loop does not seem to work – johnjohn1 Dec 04 '19 at 18:22
@johnjohn, Please tell me your right way, what you want, I do not understand – Rahul Kr Daman Dec 04 '19 at 18:29
Why I get always zero value in the cell 16,1. My code does not seem to work correctly – johnjohn1 Dec 04 '19 at 19:10

yuri · Accepted Answer · 2019-12-11T08:34:08.330

0

This will solve your problem, it uses getValues getting a range of 24x24 cells and iterating it to compare every cell value to see if it is equal to yesterday:

function isYesterday(){
  var yesterday_jobs=0;
  var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row=0;
  var col=0;
  var date;
  var yesterday = new Date(new Date().getFullYear(),new Date().getMonth() , new Date().getDate())
  yesterday.setDate(yesterday.getDate() - 1);

  range = ss.getRange(1,1,25,25).getValues()
  for (var i = 0; i < 25; i++){
    for ( var j = 0; j < 25; j++) {
      date = Date.parse(range[i][j]);    
      if ( date - yesterday.getTime() <= (24 * 60 * 60 *1000)  ){
        yesterday_jobs = yesterday_jobs + 1;
      }
    }
  }
  ss.getRange(16,2).setValue(yesterday_jobs);
}

Things that were wrong...

This is wrong and it is the reason it's not working:

      yesterday_jobs === yesterday_jobs + 1;

You should be doing:

      yesterday_jobs = yesterday_jobs + 1

Why?

Because == and === are Comparison operators, and = is an assignment operator.

What you are trying to do is to set a new value to yesterday_jobs, not to compare it, so you have to use =.

This will solve your problems with the loop assignations.

When doing a = 2 you are assigning a value to a variable:

a value is now equal to 2 value

When doing a == 2 you are asking:

Is a equal to 2 in value?

When doing a === 2 you are asking

Is a equal to 2 in value and type?

edited Dec 11 '19 at 08:34

answered Dec 05 '19 at 08:23

yuri

3,182
2
16
26

Thank you for pointing out. I did not know that. However I changed it and still my code does not work. Anythying else that could be wrong? – johnjohn1 Dec 05 '19 at 19:26
Edit and correct your code with this new things you know. For example, what I told you before, also, on the if conditions you are asking if row === i. But row does not have any value, because it's never been assigned, it's undefined. You should assign a value to it prior to make any comparison. Also, you have to format date properly to be able to find it on the spreadsheet: About date formats: https://stackoverflow.com/questions/3552461/how-to-format-a-javascript-date – yuri Dec 06 '19 at 08:14
Hey @johnjohn1, I've edited the answer, it should solve it. If so, please, select the answer as correct: https://stackoverflow.com/help/someone-answers – yuri Dec 11 '19 at 08:35
I have tried the following bases on what you recommened but still does not work. Logger.Log gives me undefined values. I have also edited my original code based on what you recommended. – johnjohn1 Dec 14 '19 at 14:28
I have made it work. However my function does not check the cells values real time for value changes. is it possible to achieve that? – johnjohn1 Dec 26 '19 at 23:02

Javascript in googlespreadsheets

2 Answers2

Things that were wrong...