I'm looking for more of a pointer to some documentation of a method here rather than an exact solution, I'm happy with JavaScript, haven't done much Apps Script and I'm moderate to OK familiar with Google Sheets functions but far from expert.

I have part of a Google Sheet with some date specific data on it like this:

... etc

In another part of a sheet I have a cell with the current value of Some-Value. This cell in fact contains a formula that totals a column on another sheet for the values. In case it's not blindingly obvious, these dates are in fact sprint end dates and the Some-Value is a count of story points extracted from JIRA. So sort of like this:

On exactly the date in the Date column I want to copy the value from the "Current Value of Some Value" cell into the cell in the "Some Value" column adjacent to that date. I don't care if that copy happens only once on that day, or several times on that day, or every hour on that day, or whatever. I just want it to happen at least once, automatically.

Should I be looking at an Apps Script function to do this, and roughly how should I do that? Or is there a simpler way of using some Google Sheets function to copy that cell?

CodePudding user response：

You could potentially do this with formulas if you are willing to enable iterative calculation for your sheet. In that case, you could then write something like =if(A2=today(),currentValueCell,B2) (I'm assuming your Date/Some-value table is in A1:Bx of a sheet and you are placing the above in B2). This will return the current value only if the date matches, and then when the dates no longer match will just maintain whatever value is already present in the cell.

CodePudding user response：

If you need to copy a value than then it's going to be changed or erased, yes, you'll need an AppScript. If you already know something, you'll more than able to set a simple function to do it.

You can look into Installable Triggers that will help you to set when and how frequently you want your script to be fired; and it will notify you if there are errors in any of that executions.

Sometimes, matching dates can be tricky, more if you have them previously written. If you have to do a timestamp, then you'll probably succeed at first. Just for have it handy: https://developers.google.com/google-ads/scripts/docs/features/dates

Remember to always use Logger.log() to track the progress of your code and what it's returning. Good luck and here you can always ask for specific struggles you may find!

(I'm no expert at programming by far, since it's copying only a value, you may find useful to record macros and watch them to learn how to copy in the formats you need - only values, paste format, etc. - if you don't copy, you can also research in .getValue() and .setValue() )