I have a button that submits information and that works. All the numbers will be positive when entered by clients. The question at hand is:

If A4 = "Sell", cells A16, A18, and A20, return those numbers as negative values instead of positive. If A4 = "Buy", then return as is (positive).

Here's what I currently have. I don't know where to input this IF statement, or even how.

function SubmitBuy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Front Sheet"); //Data entry sheet
  var dataS = ss.getSheetByName("Front Sheet"); //Data Sheet
  
  var values = [[formS.getRange("A2").getValue(),
                 formS.getRange("A4").getValue(),
                 formS.getRange("A6").getValue(),
                 formS.getRange("A8").getValue(),
                 formS.getRange("A10").getValue(),
                 formS.getRange("A12").getValue(),
                 formS.getRange("A16").getValue(),
                 formS.getRange("A18").getValue(),
                 formS.getRange("A20").getValue(),
                 formS.getRange("A28").getValue(), ]];

  dataS.getRange(dataS.getLastRow()  1, 3, 1, 10 ).setValues(values); 
  ClearCells();
}

CodePudding user response：

Get all of Column A's values and manipulate them using Array.map and Set:

function SubmitBuy() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const frontSheet = ss.getSheetByName('Front Sheet'); //Data entry sheet
  const valuesA1A28 = frontSheet.getRange('A1:A28').getValues();
  const rowsNeeded = [2, 4, 5, 8, 10, 12, 16, 18, 20];
  const criteriaRow = 4;
  const rowsToModify = new Set([16, 18, 20]);
  const valuesNeeded = [
    rowsNeeded.map((thisRow) => {
      const thisValue = valuesA1A28[thisRow - 1][0];
      if (
        rowsToModify.has(thisRow) &&
        valuesA1A28[criteriaRow - 1][0] === 'Sell'
      )
        return -thisValue;
      else return thisValue;
    }),
  ];

  frontSheet
    .getRange(frontSheet.getLastRow()   1, 3, 1, 10)
    .setValues(valuesNeeded);
  ClearCells();
}