谷歌腳本從用戶表單表中獲取值到資料表。即使我在用戶表單中插入或洗掉行

我有一種情況，我可以插入或洗掉行。所以我無法指定確切的單元格范圍

請幫助處理這段代碼，我做錯了什么（初學者），我已經嘗試了下面的代碼。我正在嘗試通過getnamedranges來實作這一點（如果錯了，請糾正我）

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

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


  //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("G4").getValue()); //Date
    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("J4").getValue()); //UserForm Number
    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C3").getValue()); //Student Name
    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C6").getValue()); //ID
    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("H8").getValue()); //Project Name
    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("G5").getValue()); //Batch Number 
    datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("cellname1").getValue()); // Get cellname1
    datasheet.getRange(blankRow, 8).setValue(shUserForm.getNamedRanges("cellname2").getValue());//Get cellname2
    datasheet.getRange(blankRow, 9).setValue(shUserForm.getNamedRanges("cellname3").getValue());// Get cellname3
    datasheet.getRange(blankRow, 10).setValue(shUserForm.getNamedRanges("cellname4").getValue());// Get cellname4
    datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("C9").getValue());// Summary
   
    // 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
    
    ui.alert(' "New Data Saved - Emp #'   shUserForm.getRange("C3").getValue()  ' "');

[![sample1][1]][1] [1]: https://i.stack.imgur.com/UxCdT.png

uj5u.com熱心網友回復：

請注意，您在 2 個名稱之間存在差異：TotalPresenttvs TotalPresent！

你可以直接使用shUserForm.getRange("TotalPresentt").getValue()

嘗試這個

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

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

}

標籤：谷歌应用脚​​本 谷歌表格

上一篇：在谷歌表格中的逗號后創建新行？

下一篇：如何根據來自2個陣列的多個條件創建總數量