Paste COUNTIF Formula Results, Not The Formula

Ive been wrestling with this for hours.

I have a script which every 24 hours takes the value from another sheet, and stores it into a cell. So we can keep a running order...

like

10/4/23 - 10

11/4/23 - 15

12/4/23 - 24

etc etc

Works fine....however, the problem I have is that my script works by pasting the formula, not the results of the formula. So what I actually end up with is a sheet that constantly updates itself, like....

10/4/23 - 10

11/4/23 - 10

12/4/23 - 10

Does anyone know a method whereby instead of pasting the formula, I can paste the output of the formula? Or even a method to copy the result, and paste it as value only?

My script is here;

function addDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  sh.appendRow([d,'=COUNTIF(currentAffiliates!C:C,"Joined")','=COUNTIF(currentAffiliates!C:C,"Rejected")']);
}

I believe your goal is as follows.

• You want to put the actual values instead of the formulas of '=COUNTIF(currentAffiliates!C:C,"Joined")','=COUNTIF(currentAffiliates!C:C,"Rejected")'.

In this case, how about the following modification?

Modified script:

function addDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  
  // I modiifed the below script.
  var { joined, rejected } = ss.getRange("'currentAffiliates'!C:C").getDisplayValues()
    .reduce((o, [c]) => {
      ["joined", "rejected"].forEach(e => {
        if (e == c.toLowerCase()) {
          o[e]++
        }
      });
      return o;
    }, { joined: 0, rejected: 0 });
  sh.appendRow([d, joined, rejected]);
}

• When this script is run, the actual values are appended instead of the formulas of '=COUNTIF(currentAffiliates!C:C,"Joined")','=COUNTIF(currentAffiliates!C:C,"Rejected")' to the sheet.

• In this case, I thought that the following modification might be able to be also used.

  • From

      var { joined, rejected } = ss.getRange("'currentAffiliates'!C:C").getDisplayValues()
        .reduce((o, [c]) => {
          ["joined", "rejected"].forEach(e => {
            if (e == c.toLowerCase()) {
              o[e]++
            }
          });
          return o;
        }, { joined: 0, rejected: 0 });
    

  • To

      var [joined, rejected] = ["joined", "rejected"].map(e => range.createTextFinder(e).matchCase(false).matchEntireCell(true).findAll().length);
    

References:

• reduce()
• forEach()