How do I code this google sheets IFS function into app script code?-CodePudding

This line of code was imported into app script via google sheets macro record, where it was written in a google sheet as a formula. How do I convert this into pure app script, which executes the IFS and then inserts the output into the corresponding cells?

What I'm trying to learn is, basically, how I code if cell A1 = "var 1", B1 = "var 2", and if A1 = "var 7", B1 = "var 8".

In other words, how to set a variable in one cell to cause a variable to be set in another.

sheet.getCurrentCell().setFormulaR1C1('=IFS(R[0]C[1]="Owner Withdrawal","Dividends",R[0]C[1]="Supplies","Expenses",R[0]C[1]="Services","Expenses",R[0]C[1]="Cash", "Assets", R[0]C[1]="Equipment", "Assets", R[0]C[1]="Accounts Payable", "Liabilities", R[0]C[1]="Owner Equity", "Equity", R[0]C[1]="Retained Earnings", "Equity", R[0]C[1]="Revenue","Revenue", R[0]C[1]="Accounts Receivable", "Revenue")');```

CodePudding user response：

Apps Script has no facilities to evaluate or "run" spreadsheet formulas. To make it work without using formulas, you have to implement identical logic in pure JavaScript, perhaps like this:

const targetCell = sheet.getCurrentCell();
const sourceCell = targetCell.offset(0, 1);
const value = sourceCell.getValue();
let result;
switch (value) {
  case 'Owner Withdrawal':
    result = 'Dividends';
    break;
  case 'Supplies':
    result = 'Expenses';
    break;
  // ...
  default:
    result = NaN;
}
targetCell.setValue(result);

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.