0

Sorry beginner and just starting to try Apps Script just for a few weeks. I'm create a form for my Inventory Tracker with few button 'Search', 'Clear Form', 'Save Data', 'Update Data' and 'Delete Data'. No issue for others button except for 'Search' because it's only shows the latest data from my data Tab. Let say in my data have few line part number "ABCD". it's only pull out the latest not all "ABCD". Tried many way but it's same. Please refer attached photo.

How we can pull out the data without any filter to latest data. What I'm looking are all data populated once I'm click the search button. I'm using below script. Need your help on this.

const ss = SpreadsheetApp.getActive();
const dataSheet = ss.getSheetByName('Data');
const formSheet = ss.getSheetByName('Form');
const formRanges = ['A29', 'B29', 'C29', 'D29', 'E29', 'F29', 'G29', 'H29', 'I29', 'J29'].map(rangeA1 => formSheet.getRange(rangeA1));
const searchKeyRange = formSheet.getRange('B3');
var SEARCH_COL_IDX = '0';

function Search() {
  const searchKey = searchKeyRange.getDisplayValue().trim().toLowerCase();
  if (!dataSheet.getDataRange().getDisplayValues().some(function (row) {
    if (searchKey === row[SEARCH_COL_IDX].trim().toLowerCase()) {
      formRanges.forEach((range, index) => range.setValue(row[index]));
      return true;
    }
  })) {
    ss.toast('Could not find a match for "' + searchKey + '" in column ' + (SEARCH_COL_IDX + 1) + '.');

  }
}
1
0

Try Array.filter like this:

const formRanges = formSheet.getRange('A29:J');

function Search() {
  const searchKey = searchKeyRange.getDisplayValue().trim().toLowerCase();
  const results = dataSheet.getDataRange().getDisplayValues()
    .filter(row => searchKey === row[SEARCH_COL_IDX].trim().toLowerCase());
  if (!results.length) {
    ss.toast('Could not find a match for "' + searchKey + '" in column ' + (SEARCH_COL_IDX + 1) + '.');
    return;
  }
  formRanges
    .clearContent()
    .offset(0, 0, results.length, results[0].length)
    .setValues(results);
}
1
  • Thanks for your reply. It's working. Thanks a lot for your help
    – Hazarizan
    Apr 9 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.