Submit, Search, and Update from one spreadsheet to another spreadsheet (ID)-CodePudding

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

Please help to enhance this below code (thanks to @[Yuri Khristich][2])

  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(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()  ' "');

        
   }
}```


-code by-----------------------


  [1]: https://i.stack.imgur.com/Yrfje.jpg
  [2]: https://stackoverflow.com/users/14265469/yuri-khristich

CodePudding user response：

I guessed your goal is as follows.

You want to copy the values from UserForm of PRINTNOTE_A to DataSheet of WB-Datasheet 2.
You want to copy the values of cells C3,C7,C8,D8,D6,D4,E8,E19,E20,E21,E22 and B10:E18 of UserForm sheet to the same row.
You want to search the column "A" of DataSheet sheet using the value of cell B1 of UserForm sheet, you want to put the values in the same row.
You want to achieve this using Google Apps Script.

By guessing your question, I propose a sample script as follows.

In order to retrieve the values from the distributed cells, in this sample script, I used Sheets API.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google Services.

From your script, this script is used as the container-bound script of PRINTNOTE_A. So, please copy and paste this script to the script editor of PRINTNOTE_A Spreadsheet.

function sample() {
  const dstSpreadsheetId = "###"; // Please set the destination Spreadsheet ID (WB-DataSheet_B).
  const dstSheetName = "DataSheet";
  const srcSheetName = "UserForm";

  // Retrieve values from source sheet and create an array and search value.
  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ranges = ["B1", "C3", "C7", "C8", "D8", "D6", "D4", "E8", "E8", "E19", "E20", "E21", "E22", "B10:E18"];
  const [search, ...values] = Sheets.Spreadsheets.Values.batchGet(srcSpreadsheet.getId(), { ranges: ranges.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.flatMap(({ values, range }) => {
    if (range.includes("B10:E18")) return values.flat();
    return values[0][0];
  });

  // Put the array to the destination sheet using the search value.
  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstSheetName);
  const range = dstSheet.getRange("A2:A"   dstSheet.getLastRow()).createTextFinder(search).findNext();
  if (range) {
    dstSheet.getRange(range.getRow(), 2, 1, values.length).setValues([values]);
  } else {
    dstSheet.getRange(dstSheet.getLastRow()   1, 1, 1, values.length   1).setValues([[search, ...values]]);
  }
}

From your replying of but new values are not getting submitted in the next blank row., I updated my proposed answer. In the current script, when the search value is not found, the values are appended to the destination sheet.

Note:

This sample script is for your provided 2 Spreadsheets. And, I tested this script using your provided Spreadsheets. So when you changed the Spreadsheet and your actual Spreadsheets are different from your provided sample Spreadsheet, this script might not be able to be used. Please be careful about this.

Sample script:

Note:

References: