CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
delatbabel
delatbabel

Reputation: 3681

Automate copying a value from one cell to another on a specific date

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:

Date Some-Value
1 Jan 2023 123
15 Jan 2023 456

... 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:

Current Value of Some-Value
345

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?

Upvotes: 0

Views: 1739

Answers (3)

The God of Biscuits
The God of Biscuits

Reputation: 3147

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.

EDIT Ah yes, I forgot that the initial state of a self-referencing IF is zero (rather than null) until a TRUE occurs. Try =if(A2=today(),currentValueCell,if(B2<>0,B2,)) to hide the initial zero generated when the date in A2 is not equal to TODAY().

Upvotes: 2

delatbabel
delatbabel

Reputation: 3681

In case anyone else comes across this in future, here is the somewhat creaky Apps Script function I developed to do this. It's my first Google Apps Script function so feel free to tear it to shreds.

I then added a trigger to run this function weekly, which was easier than I had thought -- a bit of google searching for Apps Script time based triggers found me the answer.

Nonetheless thanks to the people who answered earlier because the clues you gave me helped me find the answer in the end.

function updateUnderTest() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = activeSheet.getSheetByName('Supporting Data');

  // I12 is the cell that gets updated daily with the story point count
  var value = sourceSheet.getRange('I12').getValue();

  // D3:E27 is the range containing date : value pairs
  var data = sourceSheet.getRange("D3:E27").getValues();

  // epochNow will be the current epoch time, in milliseconds
  var epochNow = new Date().valueOf();

  // Look through the data range to find a matching date
  for (nn=0;nn<data.length;++nn) {
    // If a match is found to the nearest day, break the loop
    if ((data[nn][0].valueOf() < epochNow) && ((epochNow - data[nn][0].valueOf()) < (24*3600*1000) )) {break};
  }

  // only update if we found a match
  if (nn < 24) {
    sourceSheet.getRange(nn+3, 5).setValue(value);
  }
}

Upvotes: 0

Mart&#237;n
Mart&#237;n

Reputation: 10117

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() )

Upvotes: 1

Related Questions