Google Apps脚本-将数据复制到其他工作表并追加 [英] Google Apps Script - Copy data to different worksheet and append

问题描述

我已经看过所有以前可以找到的问题，但是我似乎永远无法使不同的示例适合我的尝试.

I have looked at all the previous questions I could find, but I can never seem to make the different examples fit what I'm trying to do.

我有一些将由不同用户填写的表格.我希望在All！H35的下拉列表中选择是"后，将数据"表上的数据复制到格式相同的单独电子表格中.每个团队都会有一个表格(来源")，每天都要填写一次，理想情况下，他们都将数据复制到同一主电子表格(目标")中的下一个空行中，从而创建所有清单的运行清单工作.

I have forms that will be filled out by different users. I want the data on the "Data" sheet to be copied to an identically formatted separate spreadsheet upon choosing "Yes" in the dropdown in All!H35. There will be a form ("Source") for each team that will be filled out daily and ideally they all copy data to the next empty row(s) in the same master spreadsheet ("Destination"), creating a running list of all the work.

[来源] [1]

[目的地] [2]

[Destination][2]

它正在部分工作，但即使调整了其余的列和行，也只能获取第一个单元格.

It's partially working, but only grabbing the first cell, even after adjusting for the remaining columns and rows.

// Get the contents of a cell in srcSheet
  var range = srcSheet.getRange(1, 1, 16, 38);
  var values = range.getValues();

  // place it in the last row if the destination cell
  var range = srcSheet.getRange(1, 1, 16, 38);
  var values = range.getValues();

最后使它起作用.这是可供参考的工作代码.

Finally got it to work. Here's the working code for reference.

function CopyRange() {
 var sss = SpreadsheetApp.openById('1zxyKx4GsBgD7hRzWT3TSvJto-R-rJoNwLu-0s5zFtko'); //replace with source ID
 var ss = sss.getSheetByName('DataTS1'); //replace with source Sheet tab name
 var range = ss.getRange('A2:AL17'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1DrCgrLIrybN4a-UqlBsGSJy5p8XBPWNR2ftviwTA5_Y'); //replace with destination ID
 var ts = tss.getSheetByName('All Data'); //replace with destination Sheet tab name

 ts.getRange(ts.getLastRow()+1, 1,16,38).setValues(data); //you will need to define the size of the copied data see getRange()

}

推荐答案

如果您想知道如何将数据从一个工作表复制到属于不同电子表格的另一工作表，则可以执行以下操作:

If you want to know how to copy data from one sheet to another sheet that belongs to different spreadsheet, then you can do the following:

function myFunction() {

  // source spreadsheets
  var sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sourceSpreadSheet.getSheets()[0];

  //destination spreadsheet
  var destinationSpreadSheet = SpreadsheetApp.openById('SPREADSHEET-ID');
  var destSheet = destinationSpreadSheet.getSheets()[0]; 

  // Get the contents of a cell in srcSheet
  var range = srcSheet.getRange(1, 1);
  var values = range.getValues();

  // place it in the last row if the destination cell
  var range = srcSheet.getRange(1, 1);
  var values = range.getValues();
  // get the last row
  var lastRow = destSheet.getLastRow();
  destSheet.getRange(lastRow + 1,1).setValue(values);

}

请注意，您可以复制范围，而不是单个单元格.请查看以下链接以获取更多信息:

Please note that you can copy a range rather than a single cell. Please look at the following links for more info:

https://developers.google.com/apps-script/reference /spreadsheet/range

注意:我注意到您的源电子表格和目标电子表格不是相同格式的"，但是在您的问题中您说它们是相同的

Note: I noticed that you source and destination spreadsheets are not "identically formatted", but in your question you said they are identical

这篇关于Google Apps脚本-将数据复制到其他工作表并追加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！