Why won't my macro correctly copy/paste values only after the first instance?

Question

Updated everything to include the ultimate goal of this spreadsheet and the issue I encountered during early testing so that if there's a better way to do this that someone with experience can identify, I can change my approach and do that instead.

Link to copy of sheet here

The Issue: This macro uses paste special (values only) twice, but it only correctly pastes the first one and does nothing for the second. For example, the the first instance copies data from C6 and pastes only the value to F6, which it does. The second copies from K6 and pastes only the value to M6, which it does not do. Everything else is functioning correctly except for this.

For context, this macro also copies a formula down in between the two copy/paste actions. You'll see the code for that below as well. It's working as intended, so you can ignore that.

With help from below, we identified the issue was that the second paste values only function wasn't working because it was copying the cell before that cell could finish summing its formula.

For example, let's say K6 was =sum(K4+K5) with K4=1 and k5=4. Before the sum could record the answer, "5" in that cell, the copy was grabbing the empty data and pasting it, thus pasting nothing.

I tried setting up a delay using sleep, but it was not a consistent solution. It only works about 50% of the time. If it's possible to set up a delay based around letting a formula finishing its output, that would be a perfect solution. If anyone has experience with a similar issue or suggestions for optimization threads, please link below. I'll loop back if I find a solution.

The Goal and Backstory: This is a mock-up of an internal tracker we use to gauge our availability (mins) against the project (in mins) to determine roughly when we have the project complete. We use this tracker to determine if something will be late, if we have the availability from our team to take on more projects, etc. It's all manual, and we don't have anyone particularly strong with scripts.

Right now, our solution is to manually enter in our availability in each cell with the assistance of basic sum formulas. This takes a lot of time and is prone to human error. So, I'm trying to find a macro that will do the math for us with my very limited experience in scripts/macros.

In the "The Ideal" tab, I've manually created an example of what I'm ultimately working towards -- a macro that sums and enters in data based on availability vs. project mins. I'm obviously new to this, so there are inevitably inefficiencies and I may be completely on the wrong track. If so, please tell me and offer me an explanation as if you were speaking to a grandparent with basic computer knowledge.

Thanks everyone for your help!

Here's the code:

function TestCalc1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('D5:H5').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D5:H6'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('I6').activate();
  spreadsheet.getRange('H6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('H6').activate();
};

Answer 1

Try this,

function TestCalc1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var cellsD5H5 = spreadsheet.getRange("D5:H5").getValues();
  spreadsheet.getRange("D6:H6").setValues(cellsD5H5);
  var cellH6 = spreadsheet.getRange("H6").getValues();
  spreadsheet.getRange("I6").setValues(cellH6);
  //spreadsheet.getRange("H6").activate();
};

getValues() - https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()