Google表格脚本，用于将一张工作表上的几个单元格值传递给新的工作表行

Question

我看了又看，试图改编一些剧本，但被卡住了。

有人可以帮助/告诉我如何在一张纸上添加特定单元格的集合，并用这些值填充第二张纸上的下一个可用行吗？请。这是我所拥有的：

function AddToDB2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("PurchaseOrder");
  var targetSheet = target.getSheetByName("DataBase");
  var range = ss.getDataRange();
  var values = range.getValues();
  var date1 =  values[1][6]; //these values are collected from multiple cells not in a specific row or col
  var po =     values[2][6];
  var vendor = values[7][0];
  var shipto = values[7][3];
  //this was for testing that the values were grabbed correctly.
  var data =  [date1,po,vendor,shipto]; 
  // I don't think this part is even close
  var lastRow = targetSheet.getLastRow()  
  var rowData = values[0]
  var newData = []
  for(n=1;n<lastRow;++n){newData.push(rowData)}
  targetSheet.getRange("C2:H"+lastRow).setValues(newData);
}

---

这是我想出的另一种方式：

function addToDB() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("PurchaseOrder");
  var targetSheet = target.getSheetByName("DataBase");
  var sourceRangePO = sourceSheet.getRange("G3");
  var poValues = sourceRangePO.getValues();
if(poValues != "") {
  var sourceRangeDate = sourceSheet.getRange("G2");
  var targetRangeDate = targetSheet.getRange("A2");
  var dateValues = sourceRangeDate.getValues();
  targetRangeDate.setValues(dateValues);
  var sourceRangePO = sourceSheet.getRange("G3");
  var targetRangePO = targetSheet.getRange("B2");
  var poValues = sourceRangePO.getValues();
  targetRangePO.setValues(poValues);  
  var sourceRangeVendor = sourceSheet.getRange("A8");
  var targetRangeVendor = targetSheet.getRange("C2");
  var vendorValues = sourceRangeVendor.getValues();
  targetRangeVendor.setValues(vendorValues);
  var sourceRangeShipTo = sourceSheet.getRange("D8");
  var targetRangeShipTo = targetSheet.getRange("D2");
  var shipToValues = sourceRangeShipTo.getValues();
  targetRangeShipTo.setValues(shipToValues);
  targetSheet.insertRowAfter(1);
  Browser.msgBox("Your PO has beed saved!");
  }
else { Browser.msgBox("Please enter a PO number"); }
}

Answer 1

你非常接近。试试这个。我对你的设置值C：H有点困惑。您只有 4 个要设置的值。我将它们设置为 D-G。您可以根据需要进行调整。

function AddToDB2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("PurchaseOrder");
  var targetSheet = ss.getSheetByName("DataBase");
  var range = sourceSheet.getDataRange();
  var values = range.getValues();
  var date1 =  values[1][6]; //these values are collected from multiple cells not in a specific row or col
  var po =     values[2][6];
  var vendor = values[7][0];
  var shipto = values[7][3];
  var data =  [date1,po,vendor,shipto]; 
  var lastRow = targetSheet.getLastRow()  
  var newData = []
    for(n=0;n<data.length;++n){newData.push([data[n]])}
  var rng =targetSheet.getRange(lastRow+1,1,1,4) // Col A-D
  rng.setValues([newData]);
 }