Search and return data using getRange() and getValue

Question

I am trying to create a sheet (using Google Sheets) for our volunteers to search for, update, and/or add mentoring information (javascript).

I started with the option to search (function onSearch) and it appears to work but the information does not appear in the sheet (attached FYI). I'd appreciate help in making this run.

date entry sheet

REVISED:

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet(); var formSS = ss.getSheetByName("Form1"); var str = formSS.getRange("D3").getValues()[3]; //Search for info entered in Form1$D3

var datasheet = ss.getSheetByName("TRACKING"); var values = datasheet.getRange(2,1,2); //Datasheet where info will be retrieved

if (values == str) {
var values1 = values.getValues(); //// get the tracking data if it matchs search request
var i = 1;

myFunction().onSearch = i < values.length; i++;

{
  var output = datasheet.getRange();                         ///retrieve information from the Tracking spreadsheet and 
                                                                  //populate the information in the appropiate cells.
              formSS.get("E8").datasheet.getValue(1),
            formSS.getRange("E10").getdatasheet.getValue(2),
            formSS.getRange("E12").datasheet.getValue(3),
            formSS.getRange("E14").datasheet.getValue(4),
            formSS.getRange("J8").datasheet.getValue(5),
            formSS.getRange("J10").datasheet.getValue(6),
            formSS.getRange("J12").datasheet.getValue(7),
            formSS.getRange("J14").datasheet.getValue(8);   
           return }}}

function onSearch() {

     var SEARCH_COL_IDX=0;
     var RETURN_COL_IDX=0;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSS = ss.getSheetByName("Form1");
``  var datasheet = ss.getSheetByName("TRACKING");
    var str = formSS.getRange("D3").getValues()[3]; //column Index 
    var values    = ss.getSheetByName("Form1").getDataRange().getValues();
    for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {
      RETURN_COL_IDX = i+1;
      
      var values = [[formSS.getRange("E8").datasheet.getValue(1),
                formSS.getRange("E10").getdatasheet.getValue(2),
                formSS.getRange("E12").datasheet.setValue(3),
                formSS.getRange("E14").datasheet.getValue(4),
                 formSS.getRange("J8").datasheet.getValue(5),
                formSS.getRange("J10").datasheet.getValue(6),
                formSS.getRange("J12").datasheet.getValue(7),
              formSS.getRange("J14").datasheet.getValue(8)]];

}
}
}

Based on the information you provided, I'm not sure what you want to accomplish. Can you provide a copy of the spreadsheet you are working on, free of sensitive information, clearly indicating the current data and the expected outcome? — Iamblichus
1st thanks for your response.I am prohibited from sharing the organization's documents, it wont even allow me to send to my personal email and send. — Ann
In that case, consider providing more screenshots and expand on your explanation in order to clarify this. Your code is pretty messy so I'm not sure what is your desired outcome. — Iamblichus
I'm the Director of a mentoring program, we currently manage the mentee / mentors using GoogleShts. We have several sheets (a sheet for mentors profile, for mentees profile & a sheet for tracking & updating the mentoring relationships). Each worksheet has 1000s of records. Volunteers, starting soon who will match & track the mentor/mentee relationships. Streamlining the process. Volunteers will search, create & edit records using the datasheet (attached). Once the search button is clicked, the relevant info from the "TRACKING" shld be retrieved & populated into the specified cell in "FORM1". — Ann
I just saw your response after I sent the details. Thank you for responding. If that doesn't help, I'll recreate the spreadsheet on my personal email and send it tomorrow. (This afternoon). — Ann

Ann Ann · Accepted Answer · 2021-04-13T04:40:08

Thanks for responding. No one had the answer, and I even read that what I was asking is not available in Google Sheets. I decided to use the filter function for each cell instead.

B3 is the search field TRACKING!C:C is the sheet to retrieve the information Tracking!E:E is the matched column to return information.

I am new here and at programming but I hope this helps someone.

=IFERROR(FILTER(TRACKING!C:C,TRACKING!E:E=B3),TRUE)

Search and return data using getRange() and getValue

1 Answers