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