I am trying to figure out how to retrieve records but due to my limited knowledge, I can't seem to figure out how to retrieve multiple rows with the same ID. I would really appreciate it if you can help. I know I can use query to do that but I want to be able to modify the list if needs be and have the changes be reflected in the list.

Please see the link below for the sample sheet: https://docs.google.com/spreadsheets/d/15d-h5mDzOqSVPwZgBTudlxqWBTxRUa2sFNZYflE7vMo/edit?usp=sharing

function searchRecord() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var shData = ss.getSheetByName ('list');
  var shDetail = ss.getSheetByName('detail'); 

  // Record 
  var searchValue = shData.getRange('B3:C3').getValue(); // Name to match 
  var values    = shData.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

  var valuesFound=false; //variable to store boolean value

  for (var i = 0; i < values.length; i  ) {

    var rowValue = values[i]; //declaraing a variable and storing the value
 
    //checking the first value of the record is equal to search item
    if (rowValue[0] == searchValue) {

      // Navigate to Details Sheet
      try{
      ss.setActiveSheet(ss.getSheetByName('detail'));
       }  catch(err){
      }
      
      // Retrive Records
      
        shDetail.getRange('C2').setValue(rowValue[1]); // Name
        shDetail.getRange('C4').setValue(rowValue[0]); //ID
        shDetail.getRange('C6').setValue(rowValue[3]); // Price
        shDetail.getRange('B9:C15').setValue(rowValue[2]);

      return; //come out from the search function
      
      }
  }
    if(valuesFound==false){
  var ui = SpreadsheetApp.getUi();
  ui.alert('No record found!');
  return;
  }
}

CodePudding user response：

In your situation, how about the following modification?

Modification points:

• In your script, when shDetail.getRange('B9:C15').setValue(rowValue[2]); is used, only one value of column "C" is used. By this, all rows are the one value. I thought that this might be the reason for your issue. And also, when I saw your Spreadsheet, I confirmed that the columns "B" and "C" are merged.

In your situation, I would like to propose the following flow.

1. Retrieve values from the cells "A6:D" from "list" sheet.
2. Create an object for searching the value of "B3" of "list" sheet.
3. Put the values to each cell of "C2", "C4", "C6" and "B9:B15" to the "detail" sheet using the object.

When this flow is reflected in the script, it becomes as follows.

Modified script:

function searchRecord() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shData = ss.getSheetByName('list');
  var shDetail = ss.getSheetByName('detail');

  // I modified below script.
  var searchValue = shData.getRange('B3').getValue(); // Cells B3:C3 are merged.

  // 1. Retrieve values from the cells "A6:D" from "list" sheet.
  var values = shData.getRange("A6:D"   shData.getLastRow()).getValues();
  
  // 2. Create an object for searching value of "B3" of "list" sheet.
  var { obj } = values.reduce((o, [a, b, c, d]) => {
    if (a != o.temp && a != "") {
      o.temp = a;
      o.obj[a] = { "C4": a, "C2": b, "B9:B15": [[c]], "C6": d };
    } else {
      o.obj[o.temp]["B9:B15"].push([c]);
    }
    return o;
  }, { obj: {}, temp: "" });
  
  // 3. Put the values to each cells of "C2", "C4", "C6" and "B9:B15" to the "detail" sheet using the object.
  Object.entries(obj[searchValue]).forEach(([k, v]) =>
    shDetail.getRange(k).setValues(k == "B9:B15" ? v.length != 7 ? [...v, ...Array(7 - v.length).fill([null])] : v : [[v]])
  );
}

• In this modified script, the values are retrieved from the column "C" of "list" sheet as an array. By this, these values can be put to the cells "B9:B15" of "detail" sheet.

References:

• reduce()
• forEach()

CodePudding user response：

Retrieve Multiple Rows and Display Detail

function searchRecord() {
  const ss = SpreadsheetApp.getActive();
  const dat = ss.getSheetByName('list');
  const det = ss.getSheetByName('detail');
  const searchValue = dat.getRange('B3').getValue();
  const vs = dat.getRange("A6:D"   dat.getLastRow()).getValues();
  let f = { pA: [] };
  let prev = '';
  //collect detail info
  vs.forEach(r => {
    if (r[0] == searchValue || prev == searchValue) {
      if (!f.hasOwnProperty(r[0])) {
        f[r[0]] = { name: r[1], id: r[0], price: r[3], ingredients: [] };
        f[r[0]].ingredients.push(r[2]);
        f.pA.push(r[0]);
      } else {
        f[r[0]].ingredients.push(r[2]);
      }
      if(r[0])prev = r[0];
    }
  });
  //display detail
  if(f.pA && f.pA.length == 1) {
    det.getRange('C2').setValue(f[pA[0]].name);
    det.getRange('C4').setValue(f[pA[0]].id);
    det.getRange('C6').setValue(f[pA[0]].price);
    det.getRange(9,2,f[pA[0]].ingredients.length,1).setValues(f[pA[0]].ingredients)
  }
}