Home > Enterprise >  submit data from one workbook to another workbook
submit data from one workbook to another workbook

Time:02-21

I need to post/submit data from the source workbook to the destination workbook(id) > worksheet

I have achieved this task by using the below code, but I need help posting to a different workbook > worksheet

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

/ Function to submit the data to DataSheet sheet
function submitData() {

  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var shUserForm = myGooglSheet.getSheetByName("UserForm"); 
  var datasheet = myGooglSheet.getSheetByName("DataSheet");//I guess here need some changes:)
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Submit", 'Do you want to submit the data?', ui.ButtonSet.YES_NO);
  if (response == ui.Button.NO) {
    return;
  }

  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
  datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("TotalPresentt").getValue());// TotalPresent
  datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("SoundRoomDay").getValue());// SoundRoomDAy
  datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("GroupDay").getValue());// GroupDay
  datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("TotalDays").getValue());// TotalDays

  datasheet.getRange(blankRow, 12).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
  datasheet.getRange(blankRow, 13).setValue(Session.getActiveUser().getEmail()); //Submitted By

  ui.alert(' "New Data Saved - StudentID #'   shUserForm.getRange("C5").getValue()   ' "');

}

CodePudding user response:

Try

var datasheet = SpreadsheetApp.openById('id of the target spreadsheet').getSheetByName("DataSheet")

openById

  • Related