CODEWITHSUNDEEP
javascriptsearchgoogle-sheetsgetvalue
Ann
Ann

Reputation: 9

Search and return data using getRange() and getValue

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)]];

}
}
}

Upvotes: 0

Views: 525

Answers (1)

Ann
Ann

Reputation: 9

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)

Upvotes: 0

Related Questions