Google Sheets API script: Trying to get the last row of a specific column by calling a named range

Question

First question in here, I've been getting info from here by searching posts for weeks but I'm a bit stuck right now. I'm trying to set up sending data and time stamps from several Google Spreadsheets set up as input forms for individual people, and to set each of those to send data with a script to a collating spreadsheet that will append any fresh data into columns specific for those people. Thus, I need to get either the last row of data of the specific column +1, or the first empty cell of said column.

I could do it with A1 notation calls for range, but then I would have to set each input spreadsheet up with the correct column and reordering columns would mess it up. I'm trying to call specific columns by a name string or named range (e.g., column with header of "John Doe" or named range "John_Doe") so I can then put that same name in as a variable once (e.g. name = John_Doe for rest of function) and not have to worry about messing up column number typos. My comments have some alternatives I've been trying too.

As a bonus, I'm trying to offset a time stamp for each submission one column right of the first cell with data put in it, but I think that will be easier to figure out if I can correctly call the starting empty cell of a specific column by the method above.

//Use the same name for this variable as the protected range name for that person. E.g., John_Doe
//****GLOBALS****
//var name = "John_Doe"

function SendDataAndTimestamp() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 var sourceRange = ss.getRange('C6:C401');  //assign the range you want to copy
 var data = sourceRange.getValues();

 var tss = SpreadsheetApp.openById('1d0C5AfqHvaCdVA6hHZRO5pcp8xm_wJkQXN0XhHiJy60'); //replace with destination ID
 
 var ts = tss.getSheetByName('All Tallies'); //replace with destination Sheet tab name, will probably be the same on the actual version

//Debugger didn't like me using var tr = ts.getRangeByName, not sure why
 var tr = tss.getRangeByName("John_Doe");

//Time stamp info. Will need to update getRange to same specific column as the data, and to use offset from that instead of direct column number.
 var date = new Date();
 //tr.getRange(tr.getLastRow()+1, +1).setValue(date).setNumberFormat("MM/dd/yyyy HH:mm:ss");
 trLR = tr.getRange(tr.getLastRow+1, tr.getLastColumn, 396);
 trLR.setValues(data);
 //tr.getRange(tr.getLastRow()+1, tr, 396).setValues(data); 


 /*Need to add variables for finding a specific column by name
 Need to adjust getLastRow to that specific column
 Need to offset date and time stamp one right of (getLastRow+1 specific column)
 In the data, may want to check it and give prompt to user if any incorrect (not a  number between 1000 and 9999)
*/

}

Answer 1

I think I got an answer that works for my own question, eventually... based on some stuff I learned about match functions being used in scripts here: https://www.youtube.com/watch?v=oM_nSfQn-C0&list=PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw&index=16

And then a JavaScript trick from a comment on this post: Determining the last row in a single column

var Avals = ss.getRange("A1:A").getValues();

var Alast = Avals.filter(String).length;

//Use the same string for this variable as the name for the requestor for his or her column of data. E.g., John Doe
//****GLOBALS****
var name = "John Buck"

function SendDataAndTimestamp() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sss = ss.getActiveSheet();
 var sourceRange = sss.getRange('C6:C401');  //assign the range you want to copy, could make C6:C.
 var data = sourceRange.getValues();
//Variables for the sending/source spreadsheet



//Below variables for the target/receiving spreadsheet
 var tsID = SpreadsheetApp.openById('id'); //replace with destination ID
 var tss = tsID.getSheetByName('sheet name'); //replace with destination Sheet tab name.


 var lc = tss.getLastColumn();
 var lr = tss.getLastRow();

 var targetRangeNames = tss.getRange(4, 1, 1, lc).getValues()[0]; //gets array of names for matching to columns by strings.
 

 var nameIndex = targetRangeNames.indexOf(name)+1; //Index number of a specific name
 var nameRow = tss.getRange(4, 1).getRow(); //returns row 4? Would like to make this dynamic, not sure how to call the row value of a specific cell based on finding the cell by string.
 var nameCell = tss.getRange(nameRow, nameIndex, 1, 1); //returns range of the cell of the requestor
 var nameA1notation = nameCell.getA1Notation; //should give a string of the A1 notation of the cell with the requestor's name. Not sure if it works, logger did not show it. Didn't use it anyway.
 

//Above should not need to change unless variables are not needed.


var targetDataInPlace = tss.getRange(5, nameIndex, lr, 1).getValues();
var targetDataLength = targetDataInPlace.filter(String).length;
var targetRange = tss.getRange(targetDataLength+nameRow+1, nameIndex, 396, 1);
targetRange.setValues(data);
//Works, but would break if any cells without value in the prior data. May need to either change to iteration to find last data cell+1, or make sure data only sends values--which I think it does.


 //Time stamp info. Will need to update getRange to same specific column as the data, and to use offset from that instead of direct column number. Update: Didn't need to use offset, just add 1 to name column value for range.
var date = new Date();
var dateRange = tss.getRange(targetDataLength+nameRow+1, nameIndex+1);
var dateValue = dateRange.setValue(date).setNumberFormat("MM/dd/yyyy HH:mm:ss");


 /*Need to add variables for finding a specific column by name--done
 Need to adjust getLastRow to that specific column--alternate method, done
 Need to offset date and time stamp one right of (getLastRow+1 specific column)--done
 In the data, may want to check it and give prompt to user if any incorrect (not a  number between 1000 and 9999)--later
*/

}