Find Edit and Update - Search value from another spreadsheet and display to source sheet (Datasheet-CodePudding

Search function, If B1(UserForm) is equal A:A (Datasheet), then get relevant value from Datasheet to Userform to corresponding cells ["C3", "C7", "C8", "D8", "D6", "D4", "E8", "E19", "E20", "E21", "E22", "B10:E18", "B19: C22"] for editing and update purpose.

I have attached a Screenshot explaining with color code where the data should go. https://i.stack.imgur.com/Yrfje.jpg [SCREENSHOT][1]

Also Shared two spreadsheets with actual data (Userform and Datasheet) for your reference https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA/edit?usp=sharing

Maybe I need some different script to achieve this task

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form_sheet = ss.getSheetByName('UserForm');
  
  var num = form_sheet.getRange('b1').getValue();
  
  var data_sheet = ss.getSheetByName('DataSheet');
  var nums = data_sheet.getRange('a:a').getValues().flat();
  var row = nums.indexOf(num);

  if (row < 0) {
    ss.toast('Nothing was found')
    return;
  }

  row  ;
  var data1 = data_sheet.getRange('a'   row   ':g'   row).getValues().flat();
  var data2 = JSON.parse(data1.pop());
  form_sheet.getRange('c3:c8').clearContent().setValues(data1.map(x => [x]));
  form_sheet.getRange('b10:e20').clearContent().setValues(data2)
}

CodePudding user response：

thank you much @Tanaike This script works

function mainsearch() {
  const srcSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; // Please set the source Spreadsheet ID (WB-DataSheet).
  const srcSheetName = "DataSheet";
  const dstSheetName = "UserForm";

  // Retrieve values from source sheet and create an array and search value.
  const dstSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const dstSheet = dstSpreadsheet.getSheetByName(dstSheetName);
  const search = dstSheet.getRange("B1").getValue();

  // Search the value.
  const srcSpreadsheet = SpreadsheetApp.openById(srcSpreadsheetId);
  const srcSheet = srcSpreadsheet.getSheetByName(srcSheetName);
  const range = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).createTextFinder(search).findNext();
  if (!range) {
    SpreadsheetApp.getUi().alert('UserForm Number Not Found');
  }

  // Retrieve the values from the searched row.
  const values = srcSheet.getRange(range.getRow(), 1, 1, srcSheet.getLastColumn()).getValues()[0];

  // Put the values to the cells of "UserForm" sheet.
  const expandRangeList = ["C3", "C7", "C8", "D8", "D6", "D4", "E8", "E19", "E20", "E21", "E22", "B10", "C10", "D10", "E10", "B11", "C11", "D11", "E11", "B12", "C12", "D12", "E12", "B13", "C13", "D13", "E13", "B14", "C14", "D14", "E14", "B15", "C15", "D15", "E15", "B16", "C16", "D16", "E16", "B17", "C17", "D17", "E17", "B18", "C18", "D18", "E18", "B19", "C19", "B20", "C20", "B21", "C21", "B22", "C22","D19", "D20", "D21", "D22"];
  const data = expandRangeList.map((range, i) => ({ range, values: [[values[i] || ""]] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, dstSpreadsheet.getId());
}```