0

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?

JR3652
  • 435
  • 1
  • 4
  • 13
  • 3
    Have a look at: https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet – Stefan van Aalst Feb 01 '18 at 17:38
  • Thank you Stefan, this seems like a good workaround – JR3652 Feb 01 '18 at 19:09
  • Possible duplicate of [Refresh data retrieved by a custom function in Google Sheet](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) – tehhowch Jul 17 '18 at 20:55
  • Yeah, that's what Stefan posted, which I agree was a good work around and is what I ended up doing – JR3652 Jul 18 '18 at 21:59

1 Answers1

0

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;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54