How can I add data and save a cell data in Google sheet-CodePudding

How can I add up and save a cell data in Google sheet, if the PaymentStatus is Un Paid and Game Day is Friday, then $10 should be display in R4 cell. Now for another date, if the PaymentStatus is Un Paid and Game Day is Sunday,then $5 should be added to previous value $10 and final $15 should display in R4 cell, is that possible using Google sheet?

function storeValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; // sheets are counted starting from 0
  var cell = sheet.getRange("Q4"); 
  var data = cell.getValue()
  var dataList = new Array();
   data  = data;
   dataList = data;
  var cell1 = sheet.getRange("R4"); 
   cell1.setValue(dataList);
}

For the date 20-Feb-2022, if the PaymentStatus is Un Paid and GameDay selection is Friday, $10 should display in R4 cell of the OutStanding Due Amtcolum. For a different date, if the user select PaymentStatus is Un Paid and GameDay selection is Sunday, the $5 should be added with previous amount $10 to display the OutStanding Due Amt due column in R4 cell, incase if Friday is again selected, the outstanding due amount will be $20.

CodePudding user response：

I strongly recommend you use the formula SUMIFS. Docs here. Learning this formula will make your life easier.

CodePudding user response：

You should update your Apps Script code to this:

function storeValue() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0]; // sheets are counted starting from 0
    var paymentStatus = sheet.getRange(1, 4, 100, 1).getValues();
    var gameDay = sheet.getRange(1, 6, 100, 1).getValues();

    for (let i = 0; i < paymentStatus.length; i  ) {
        if (paymentStatus[0][i   4] == 'Un Paid' && gameDay[0][i   4] == 'Friday')
            sheet.getRange(i   4, 18).setValue(10)
        else if (paymentStatus[0][i   4] == 'Un Paid' && gameDay[0][i   4] == 'Sunday') {
            var rCol = sheet.getRange(i   4, 18).getValue()
            sheet.getRange(i   4, 18).setValue(5   int(rCol))
        }
    }
}

The changes that have been made are the following:

added the paymentStatus and gameDay variables which store the values for these respective columns from the sheet by using getRange and getValues;
added a for loop in order to loop through these values;
the if conditions checks if the current row (starting from row 4) fulfills the conditions you imposed: paymentStatus is Un Paid and gameDay is Friday OR if the paymentStatus is Un Paid and the gameDay is Sunday, then 5 is added to the original value;

Reference