0

I'm new to App Script and I'm trying to update an existing row by matching value coming from another sheet. Basically record all the data from the "receipt" to the "record" by matching the name on the record. Thanks in advance and sorry if I'm a little confusing.

So far I tried this and not working:

function recordPayment(){
    
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var FormSheet = ActiveSheet.getSheetByName("Receipts");
var DataSheet = ActiveSheet.getSheetByName("PaymentRecords");
    
    
var TenantName = FormSheet.getRange("C7").getValue();
var AmountPaid = FormSheet.getRange("E20").getValue();
var DateOfPayment = FormSheet.getRange("E6").getValue();
var Names = DataSheet.getRange(2,1,34,1).getValues();
var NewNames = Names.map(function(r){ return r[0]; });
    
    for(var i=1; i>35; i=i+1){
    
      if(TenantName==NewNames[i]){
    
        DataSheet.getRange(i ,2).setValue(AmountPaid);
    
      }
    
    
    }
}

Receipt Record

1
  • Welcome to Web Applications The question needs mores details and focus. Please edit the question to show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Oct 21, 2021 at 19:17

1 Answer 1

1

Your script is not working as expected.

For statement

This should read for (var i=0; i<34; i=i+1){

  • i must start at zero.
    • NewNames is a "zero-based" array so you must start the loop at zero in order to evaluate the first row of the array.
  • the Condition must evaluate values up to 34.
    • We know that there are 34 rows of data but this is a zero-based array, and because the starting value is zero (i=0), you need to count up to but not including 34. If you use "i<35", then the last value of NewNames[i] will be "undefined".
  • the Condition must be < (less than).
    • The Condition is evaluated before each loop iteration and the statement is executed ONLY if the Condition evaluates to true.
    • If the Condition is > (greater than) AND if the starting value equals 1, then the Condition is immediately evaluated as false and nothing is executed at all.

getRange method

This should read DataSheet.getRange((i+2) ,2).setValue(AmountPaid);

Basically you need to add "2" (two) to the value of i to get the correct row number on DataSheet.

Each of the following require an adjustment of one row:

  • Names began on row 2 which excluded the header row. But when using getRange(row, column), the row value is the index value and this includes the header row.
  • The for loop was zero-based so to adjust its result to index-based, you must add one.

You could, if you wish, create a variable actualRow = i+2 and use this in the script

Revised script

function recordPayment(){
    
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var FormSheet = ActiveSheet.getSheetByName("Receipts");
var DataSheet = ActiveSheet.getSheetByName("PaymentRecords");
    
    
var TenantName = FormSheet.getRange("C7").getValue();
var AmountPaid = FormSheet.getRange("E20").getValue();
var DateOfPayment = FormSheet.getRange("E6").getValue();
var Names = DataSheet.getRange(2,1,34,1).getValues();
var NewNames = Names.map(function(r){ return r[0]; });
    
    for(var i=0; i<34; i=i+1){

      if(TenantName==NewNames[i]){

        // allow one row for header
        // loop is zero based. so add one row 
        // actual row = i+2
        Logger.log("DEBUG: the range to update = "+DataSheet.getRange((i+2) ,2).getA1Notation())
        DataSheet.getRange((i+2) ,2).setValue(AmountPaid);
    
      }
    
    }
}

Other comments

The number of names on "DataSheet" may increase over time. So it might be desirable to create a dynamic data range rather than hard code the number of data rows. There are several options for this, but one approach would be to use

  • use getDataRange()instead ofgetRange()`- this will get all the data without having to specify a specific start and end row. The number of rows to loop through would be the length of the array. The modified lines would look something like this:

    • var Names = DataSheet.getDataRange().getValues();
    • for (var i=0; i<NewNames.length; i++){
  • Note: this script adds only one row to determine the range to update; this is because the header row is already included in the Names range.

Dynamic script

function dynamicrecordPayment(){
    
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet();
var FormSheet = ActiveSheet.getSheetByName("Receipts");
var DataSheet = ActiveSheet.getSheetByName("PaymentRecords");
    
    
var TenantName = FormSheet.getRange("C7").getValue();
var AmountPaid = FormSheet.getRange("E20").getValue();
var DateOfPayment = FormSheet.getRange("E6").getValue();
var Names = DataSheet.getDataRange().getValues();
Logger.log("DEBUG: the names range = "+DataSheet.getDataRange().getA1Notation())

var NewNames = Names.map(function(r){ return r[0]; });
Logger.log("DEBUG: length of new names = "+NewNames.length)


  for (var i=0; i<NewNames.length; i++){
    Logger.log("DEBUG: i:"+i+" and NewNames:"+NewNames[i])

    if(TenantName==NewNames[i]){

      // allow one row for header
      var actualRow = i+1
      Logger.log("DEBUG: the range to update = "+DataSheet.getRange(actualRow ,2).getA1Notation())
      DataSheet.getRange(actualRow ,2).setValue(AmountPaid);
    
    }
      
  }

}
 
2
  • Thank you for the very fast answer Oct 25, 2021 at 23:17
  • My pleasure. If this answer was useful, perhaps you would consider "accepting" it.
    – Tedinoz
    Oct 26, 2021 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.