Date variable not updating in Google Sheets App Scripts

0
votes

So I use a google sheets spreadsheet to keep track of all of my spending during a month. I've created a function to output how much money I have spent by the current day of the month (i.e. today it would show spending to the 1st of each months sheet). It takes in a column of the date of the spending and how much was spent in that purchase.

function spentByThisDay(date, spent) {
  var d = new Date();
  var currentDate = d.getDate();
  var currDate;
  var sum =0;
  for(var i=0; i<date.length; i++){
    currDate = new Date(date[i]);
    if(currDate.getDate()<=currentDate)
      sum+=parseFloat(spent[i]);
  }
  return sum;
}

This function works fine. My issue is is that it doesn't update for the current date (for today, the 1st) on my past months sheets. I have to manually go in and delete the cell that makes the call to the function

=spentByThisDay(A2:A100,D2:D100)

and re-paste it into the cell to get it to update.

Is there any workaround to get this output to update every day or every time I open the spreadsheet?

1
google-apps-scriptgoogle-sheets
Have a look at: stackoverflow.com/questions/17341399/… – Stefan van Aalst
Thank you Stefan, this seems like a good workaround – JR3652
Possible duplicate of Refresh data retrieved by a custom function in Google Sheet – tehhowch
Yeah, that's what Stefan posted, which I agree was a good work around and is what I ended up doing – JR3652

1 Answers

0
votes

I did it this way and it seems to work okay.

function spentByThisDay(date, spent) {
  var currentDate=new Date();
  var sum =0;
  for(var i=0; i<date.length; i++){
    var currDate = new Date(date[i]);
    if(currDate.valueOf()<=currentDate.valueOf()){
      sum+=parseFloat(spent[i]);
    }
  }
  return sum;
}