googleappscript : Post data from one work book [closed]

Question

Submit values from userform (selected cells) to another google spreadsheet and then find them (search), modify and post it again. I have a source sheet (userform), I need to get values from the selected cells and ranges, submit the values to the destination sheet (another spreadsheet) using ID.

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

  var form_sheet = ss.getSheetByName('UserForm');
  
  var data1 = form_sheet.getRange(['c3:c8']).getValues().flat();
  var [num, date, name, id, project,group] = data1;

  var data2 = 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 ) {
    var new_row = [num, date, name, id, project, group, JSON.stringify(data2)];
    data_sheet.appendRow(new_row)
  } else {
    var range = data_sheet.getRange('g' + ++row);
    range.setValue(JSON.stringify(data2));
        
  }
}

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)
}

// Function to submit the data to DataSheet sheet
function oldsubmitData() {
     
  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 
 
  var shUserForm= myGooglSheet.getSheetByName("UserForm"); //delcare a variable and set with the User Form worksheet
 
  var datasheet = myGooglSheet.getSheetByName("DataSheet"); ////delcare a variable and set with the DataSheet worksheet

var namedRanges = SpreadsheetApp.getActiveSheet().getNamedRanges();
if (namedRanges.length > 1) {
Logger.log(namedRanges[0].getName());
}
  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();
  
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
  // close the dialog by clicking the close button in its title bar.
  var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);
 
  // Checking the user response and proceed with clearing the form if user selects Yes
  if (response == ui.Button.NO) 
  {return;//exit from this function
  } 
 
  //Validating the entry. If validation is true then proceed with transferring the data to DataSheet sheet
  // if (validateEntry()==true)
 {
  
    var blankRow=datasheet.getLastRow()+1; //identify the next blank row
 
    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C3").getValue()); //Date
    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("C4").getValue()); //UserForm Number
    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C5").getValue()); //Student Name
    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C6").getValue()); //ID
    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C7").getValue()); //Project
    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C8").getValue()); //Group Name
    //I stuck here, get values from moving row (E,13 to 16)
     //datasheet.getRange(blankRow, 8).setValue(shUserForm.getNamedRanges("TotalPresent").getValue());// TotalPresent
    //datasheet.getRange(blankRow, 8).setValue(shUserForm.getNamedRanges("SoundRoomDay").getValue());// SoundRoomDAy
    //datasheet.getRange(blankRow, 9).setValue(shUserForm.getNamedRanges("GroupDay").getValue());// GroupDay
    //datasheet.getRange(blankRow, 10).setValue(shUserForm.getNamedRanges("TotalDays").getValue());// TotalDays

   
    // date function to update the current date and time as submittted on
    datasheet.getRange(blankRow, 12).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
    
    //get the email address of the person running the script and update as Submitted By
    datasheet.getRange(blankRow, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By

     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 datasheet = ss.getSheetByName('DataSheet');
  var nums = datasheet.getRange('a:a').getValues().flat();
  var row = nums.indexOf(num);

  if (row < 0) return;
  
  var range = datasheet.getRange('g' + ++row);
  range.setValue(JSON.stringify(data));
    ui.alert(' "New Data Saved - StudentID #' + shUserForm.getRange("C5").getValue() +' "');

        
   }
}

Answer 1

Here are 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 "Search" button. Otherwise, SpreadsheetApp will send into script the previous value of the cell "B2".

---

Here is the modified sumbmitData() function:

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form_sheet = ss.getSheetByName('UserForm');
  
  var data1 = form_sheet.getRange(['c3:c8']).getValues().flat();
  var [num, date, name, id, project,group] = data1;

  var data2 = 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 ) {
    var new_row = [num, date, name, id, project, group, JSON.stringify(data2)];
    data_sheet.appendRow(new_row)
  } else {
    var range = data_sheet.getRange('g' + ++row);
    range.setValue(JSON.stringify(data));
  }
}

Now it appends a new row if there is no row with the given form number (column A). The previous variant of the function works more like editData(), it changes existed data only.

Answer 2

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);
}