'Find Edit and Update - Search value from another spreadsheet and display to source sheet (Datasheet to Userform)
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)
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
