-1

I am trying to code a spreadsheet to automatically distribute payment to each person. Here is the link. I want it to distribute payment without deleting old payments. Here is the script I am using:

const payDay = () => {

  // get spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet()

  // get Pay Tracking sheet
  const paySheet = ss.getSheetByName('Pay Tracking')

  // get names and amounts from M2
  const namesAmounts = paySheet.getSheetValues(2, 13, 20, 2)

  // add the amount to the corresponding sheet name
  namesAmounts.forEach(data => {

    // get sheet by name
    const sheet = ss.getSheetByName(data[0])

    // get last row in column D (= 4)
    const lRow = sheet.getRange('N2:N').getValues().filter(String).length

    // prepare data
    const newData = [['deposit', data[1], new Date()]]

    // prepare formats
    const formats = [['@', '$0.00','dd/MM/yyyy']]

    // set range
    const range = sheet.getRange(lRow + 2, 4, 1, 3)

    // set formats
    range.setNumberFormats(formats)
    
    // add new data
    range.setValues(newData)
  })
}

I am getting the error in the title whenever I try to run this script.

I tried changing the range being referenced a few different times. I was expecting it to find the name of each person and the amount they are being payed so it could be transferred to their individual sheets.

Katt K
  • 1
  • Welcome to [so]. When asking for help with code that throws an error, please add the textual error message (the line number that is throwing the error is missing). If you didn't wrote the code you should provide proper attribution. Ref. https://stackoverflow.com/help/referencing. While links to external resources might be helpful, questions on this site should be self contained. Considering this, please describe the spreadsheet and add sample data direclty into the question body. – Rubén Jan 25 '23 at 17:12

1 Answers1

0

try sheet.getRange('N2:N' + sheet.getLastRow())

Cooper
  • 59,616
  • 6
  • 23
  • 54