Please help me with the code, I am trying to post data
from source sheet, ranges from B10:E20 to destination sheet, in a single row with reference to Form number
example image

Attache workbook for your reference

https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit#gid=0

Please help!

CodePudding user response：

Post Data in one row

function postdatainoneline() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getRange('B10:E20').getValues().flat();
  sh.appendRow(vs);
}

CodePudding user response：

Here is the two functions submitData() to put data from the form to the data table and searchRecord() to search data in the data table and put it into the form (ranges C3:C8 and B10:E20):

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form_sheet = ss.getSheetByName('UserForm');
  
  var num = form_sheet.getRange('c3').getValue();
  var data = form_sheet.getRange('b10:e20').getValues();

  var data_sheet = ss.getSheetByName('DataSheet');
  var nums = data_sheet.getRange('a:a').getValues().flat();
  var row = nums.indexOf(num);

  if (row < 0) return;
  
  var range = data_sheet.getRange('g'     row);
  range.setValue(JSON.stringify(data));
}

function searchRecord() {
  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)
}

But data in column G of the data table is a JSON string. It's not very friendly for direct edit.

Note: when you change a value in the cell 'B2' you need to press Enter before you click on the button 'Search'. Otherwise SpreadsheetApp will send into script the previous value of the cell 'B2'.