從一個電子表格提交、搜索和更新到另一個電子表格(ID)-有解無憂

將用戶表單（選定單元格）中的值提交到另一個谷歌電子表格，然后找到它們（搜索），修改并再次發布。我有一個源表（用戶表單），我需要從選定的單元格和范圍中獲取值，使用 ID 將值提交到目標表（另一個電子表格）。

我附上了一個螢屏截圖，用顏色代碼解釋資料應該去哪里。https://i.stack.imgur.com/Yrfje.jpg [螢屏截圖][1]

還共享了兩個包含實際資料的電子表格（用戶表單和資料表）供您參考 https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing

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

請幫助增強以下代碼（感謝@[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

uj5u.com熱心網友回復：

我猜你的目標如下。

您想將值從UserFormof復制PRINTNOTE_A到DataSheetof WB-Datasheet 2。
您想將單元格C3,C7,C8,D8,D6,D4,E8,E19,E20,E21,E22和B10:E18作業UserForm表的值復制到同一行。
您想DataSheet使用作業表單元格的值搜索作業表B1的“A”列UserForm，您希望將這些值放在同一行中。
您想使用 Google Apps 腳本實作此目的。

通過猜測您的問題，我提出了一個示例腳本如下。

為了從分布式單元格中檢索值，在此示例腳本中，我使用了 Sheets API。

示例腳本：

在使用此腳本之前，請在 Advanced Google Services 中啟用 Sheets API。

在您的腳本中，此腳本用作PRINTNOTE_A. PRINTNOTE_A因此，請將此腳本復制并粘貼到電子表格的腳本編輯器中。

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

從您的回復中but new values are not getting submitted in the next blank row.，我更新了我提出的答案。在當前腳本中，當未找到搜索值時，會將這些值附加到目標作業表。

筆記：

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.

References:

Method: spreadsheets.values.batchGet
createTextFinder(findText) of Class Range

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/438831.html

標籤：google-apps-script google-sheets google-sheets-api

上一篇：QUERY-GROUPBY按日期計算不同的客戶

下一篇：將函式設定為字典中的值時如何不執行函式