How to Change Status Column in Google sheet after Looking Up Reference Number

Question

I am quite the 'newbie' programmer. Trying to make a simple 'status change' upon a formSubmit event. Many thanks for any help / assistance in my journey.

When the user fills out the form (https://docs.google.com/a/gregbartonmba.com/forms/d/e/1FAIpQLSccMrgAWq95SOJO-1TC74lb4ri71aZzIWCMe342KmM66d6lqw/viewform), then the script runs. - Finds the reference row with the ReferenceNumber in it (column B). - Changes the value in the Status column (column A) to the new Status.

I have some example code that I attempted to modify but I'm too much of a newbie to get it right. I think I'm close, but I'm somewhat lost... Thanks.

The sheet with code can be found here: https://docs.google.com/spreadsheets/d/1yXIggGwWg84CebcABTLxPcCd8YI7W7VJL7PmVkcjvJI/edit?usp=sharing

My coding attempt posted below...

function changeStatus(e) { 
   var Status = e.values[0];
   var ReferenceNumber = e.values[1];


// Look up the cell in the sheet that contains the ReferenceNumber.
// Find the instance in range B2:B that has the ReferenceNumber entered.
// Then, change the code of the left-most cell (Status) to the new "Status"     (entered from form submission)

/* Modified code from example at: 
 http://stackoverflow.com/questions/18482143/google-scripts-search-spreadsheet-by-column-return-rows
*/


 var searchString = ReferenceNumber;
 var ss = SpreadsheetApp.openById("1yXIggGwWg84CebcABTLxPcCd8YI7W7VJL7PmVkcjvJI"); 
var column =1; //column Index   
var columnValues = ss.getRange(2, column, ss.getLastRow()).getValues(); //1st is header row
var searchResult = columnValues.findIndex(searchString); //Row Index - 2

if(searchResult != -1)
{
    //searchResult + 2 is row index.
             SpreadsheetApp.getActiveSpreadsheet().setActiveRange(ss.getRange(searchResult + 2, 3)).setValue("found here");
}
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
   for (var i=0; i<this.length; i++)
    if (this[i].toString().indexOf(search) > -1 ) return i;

  return -1;


  /* If reference number entered does not exist, show browser pop-up that states "Reference Number
 Does not exist. Please enter a valid reference number." 
  */ 


 /* Catch any errors and then send them via email to greg@gregbartonmba.com.  
   Need error-catching in case the reference number does NOT exist in the 
   sheet.  
*/   

}

Answer 1

The first problem is that the Spreadsheet classes all use 1-based indices, so you need to use

var column = 2; //column Index 

The second problem is that you are trying to call getRange on a spreadsheet, you need:

var columnValues = ss.getSheetByName("MasterList").getRange(2, column, ss.getLastRow()).getValues();

The third problem is that you are running findIndex of a String on a String[][] object, an array of arrays of strings.
Even though you only have a column vector GAS uses a 2d array to be unambiguous as to the layout of the data.
Since in javascript arrays are only equal to each other if they refer to the exact same memory location and thus [1] == [1] being false we need to deep check the array.

Instead of Array.prototype.findIndex let's

Array.prototype.findIndexInColVector = function(search){
  if(search == "") return false;
   for (var i = 0; i < this.length; i++)
    if (this[i][0].toString().indexOf(search) > -1 ) return i;
  return -1;
}

And then we can

var searchResult = columnValues.findIndexInColVector(searchString); //Row Index - 2

The fourth problem, then, when you found the row you are trying to select ranges on the spreadsheet, not the sheet

ss.getSheetByName("MasterList").getRange(searchResult + 2, 3).setValue("found here");

Of course to adjust the status you would then use

ss.getSheetByName("MasterList").getRange(searchResult + 2, 1).setValue(Status);