Use formula inside script

Question

I would like to use a formula inside a custom function, like this for example:

function myFunction(range, value) {
    var countNumber = COUNTIF(range; value); // COUNTIF is a formula that can be used in the spreadsheet
    if (countNumber > 0) {
        return "RESULT";
    } else {
        return "OTHER RESULT";
    }
}

And then:

=MYFUNCTION(A1:A5,"VALUETOTEST")

---

I would like to simplify a huge formula:

Something like:

=IF(SUM(COUNTIFS(G182:G186;"ERROR";H182:H186;"62");COUNTIFS(G182:G186;"ERROR";H182:H186;"ALL"))>0;"ERRO";IF(SUM(COUNTIFS(G182:G186;"RETEST";H182:H186;"62");COUNTIFS(G182:G186;"RETEST";H182:H186;"TODOS"))>0;"RETEST";IF(COUNTIF(G182:G186;"UNIMPLEMENTED")>0;"UNIMPLEMENTED";"SOLVED")))

Answer 1

You have three ways of performing these actions.

1. Add the Sheet Formulas to the sheet itself in the ranges that you need. Then read the data from the result cells (wherever you set it to write to) using your GAS Function. You can then perform further processing using the results.

2. Use your GAS function to write Sheet Formulas into your sheet. Then use more GAS to read that result and process the data. The method for this can be found here: https://developers.google.com/apps-script/reference/spreadsheet/range#setFormula(String)

3. You can create a Custom Sheet Formula using GAS that you then use in your sheet. GAS can then read that result and process the information. This will require some research into JS as a whole to know how to recreate, combine, and perform the operations that you need the data in the sheet to perform.

You can find a guide to make Custom Formulas here: https://developers.google.com/apps-script/guides/sheets/functions

And a guide to JS here: http://www.w3schools.com/js/default.asp

W3 Schools has a quite comprehensive guide to JS. GAS uses all native JS methods as it is a JS coding environment. Check the GAS Reference for more on GAS-specific methods that may perform what you need.

If what you need is to check conditions and/or iterate through rows, try something like this:

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange(startRow, startColumn, numRows, numColumns);
var values = range.getValues(); //This is a 2D array; iterate appropriately
  for (i = 0; i < values.length; i++) {
    if (values[i] == conditionToCheck) {
      //perform code..OR
      //continue; <- This works to skip the row if the condition is met
    } else {
      //perform alternate code if condition is not met
    }
  }
}

As I mentioned, .getValues() creates a 2D array. If you need to iterate through columns and rows, you will need 2 for() loops like so:

for (i = 0; i < values.length; i++) { //iterates through the rows
  for(j = 0; j < values[i].length; j++) { //iterates through the columns in that current row

It is important to mention how GAS handles 2D arrays. values[i][j] denotes how much i rows there are and j columns. You can visualize like so:

values = [[A1, B1, C1],[A2, B2, C2],[A3, B3, C3]]

This is an array of arrays where the outer array is an array of the rows, while the insides are an array of cell values by column in that row.