从几个电子表格收集数据并将其复制到另一个表单中 [英] Collect Data from several Spreadsheets and copy it into another sheet

问题描述

我想将数据从一个电子表格复制到另一个电子表格。但
1.数据在几张纸上
2.只有某些列（只是为了加快速度，不需要所有列）

开始时我使用了以下脚本：

$ p $ function CopyDataToNewFile（）{
var sss = SpreadsheetApp.openById（ '0AjN7uZG ....'）; // sss =源电子表格
var ss = sss.getSheetByName（'Monthly'）; // ss =源表
//获取全部数据
var SRange = ss.getDataRange（）;
//获取A1表示法，标识范围
var A1Range = SRange.getA1Notation（）;
//获取范围
中的数据值var SData = SRange.getValues（）;

var tss = SpreadsheetApp.openById（'8AjN7u ....'）; // tss =目标电子表格
var ts = tss.getSheetByName（'RAWData'）; // ts = target sheet
//将目标范围设置为源数据的值
ts.getRange（A1Range）.setValues（SData）;


$ / code>

我从user2741中取出了一个， HREF = https://stackoverflow.com/questions/4509336/how-to-copy-a-row-from-one-google-spreadsheet-to-another-google-spreadsheet-usin>这里：



..这张照片适用于一张纸。
但现在我不确定如何从更多工作表中获取数据并对其进行分类。

谢谢大家，
Sascha

有没有人对如何做到这一点有个好主意？ >如果所有事情都发生在同一电子表格中，则此处适用。

  SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（SummarySheet）。明确（）; 
 mergeSheet2（SummarySheet，123）; 
 mergeSheet2（SummarySheet，345）; 
 mergeSheet2（SummarySheet，748）; 
 mergeSheet2（SummarySheet，293）; 
 $ b函数mergeSheet2（targetSheetName，sourceSheetName）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sourceSheet = ss.getSheetByName（sourceSheetName）; 
 var lastRow = sourceSheet.getLastRow（）; 
 var lastCol = sourceSheet.getLastColumn（）; 
 var source = sourceSheet.getRange（1,1，lastRow，lastCol）; //一些迭代后出错：范围的坐标或尺寸无效。 （第11行，文件copy-m.js） - >可能为空电子表格
 var destSheet = ss.getSheetByName（targetSheetName）; 
 var destRange = destSheet.getRange（destSheet.getLastRow（）+ 1,1）; 
 source.copyTo（destRange，{contentsOnly：true}）; 
 
 
 $ / code> 

但由于某种原因，我不明白它在什么时候运行使用不同的电子表格
错误：目标范围和源范围必须位于同一电子表格中



我试过它：

  SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（SummarySheet）。clear（）; 
 SpreadsheetApp.openById（'ID'）; 
 mergeSheet2（SummarySheet，123）; 
 
 
函数mergeSheet2（targetSheetName，sourceSheetName）{
 var ss = SpreadsheetApp.openById（'ID'）; 
 var ssd = SpreadsheetApp.openById（'ID'）; 
 var sourceSheet = ss.getSheetByName（sourceSheetName）; 
 var lastRow = sourceSheet.getLastRow（）; 
 var lastCol = sourceSheet.getLastColumn（）; 
 var source = sourceSheet.getRange（1,1，lastRow，lastCol）; //一些迭代后出错：范围的坐标或尺寸无效。 （第11行，文件copy-m.js） - >可能为空电子表格
 var destSheet = ssd.getSheetByName（targetSheetName）; 
 var destRange = destSheet.getRange（destSheet.getLastRow（）+ 1,1）; 
 source.copyTo（destRange，{contentsOnly：true}）; 
  

有什么想法？


解决方案

该解决方案比您尝试的更简单，因为您只想复制值并知道 copyTo 只能在同一电子表格中工作，只需获取来源的值并将其粘贴到目标中，如下所示：

pre $ function $ copyFromSourceToTarget（targetSheetName，sourceSheetName）{
var ss = SpreadsheetApp.openById（ 'ID'）;
var ssd = SpreadsheetApp.openById（'ID'）;
var sourceSheet = ss.getSheetByName（sourceSheetName）;
var sourceData = sourceSheet.getDataRange（）。getValues（）;
var destSheet = ssd.getSheetByName（targetSheetName）;
destSheet.getRange（destSheet.getLastRow（）+ 1,1，sourceData.length，sourceData [0] .length）.setValues（sourceData）;

$ / code>

为了使它更加灵活，我建议使用4个参数并再次简化代码：



 函数copyFromSourceToTarget（targetSheetName，targetSsId，sourceSheetName，sourceSsId）{
 var ss = SpreadsheetApp.openById sourceSsId）getSheetByName（sourceSheetName）; 
 var ssd = SpreadsheetApp.openById（targetSsId）.getSheetByName（targetSheetName）; 
 var sourceData = ss.getDataRange（）。getValues（）; 
 ssd.getRange（ssd.getLastRow（）+ 1,1，sourceData.length，sourceData [0] .length）.setValues（sourceData）; 
} 
  




I want to copy data from one spreadsheet into another one. But 1. The Data are on several sheets 2. Only certain Columns (just to speed it up, don't need all columns)

For the beginning I used the following script:

function CopyDataToNewFile() {
  var sss = SpreadsheetApp.openById('0AjN7uZG....'); // sss = source spreadsheet
  var ss = sss.getSheetByName('Monthly'); // ss = source sheet
  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('8AjN7u....'); // tss = target spreadsheet
  var ts = tss.getSheetByName('RAWData'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

}  

I took this one from user2741, which was posted here:

..this one works fine for one sheet. But now I am unsure how to get the data from more sheets and actually sort it. Does anybody has a good idea on how to do this?

Thank you all, Sascha

This one here works, if everything happens in the same spreadsheet

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet").clear();
mergeSheet2("SummarySheet", "123");
mergeSheet2("SummarySheet", "345");
mergeSheet2("SummarySheet", "748");
mergeSheet2("SummarySheet", "293");

function mergeSheet2(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var lastRow = sourceSheet.getLastRow();
  var lastCol = sourceSheet.getLastColumn();
  var source = sourceSheet.getRange(1,1,lastRow,lastCol); // Error after some iterations: The coordinates or dimensions of the range are invalid. (line 11, file "copy-m.js") -> might be empty spreadsheet
  var destSheet = ss.getSheetByName(targetSheetName);
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo(destRange, {contentsOnly: true});

}

but for some reason I don't get it run when using different spreadsheets Error: "Target range and source range must be on the same spreadsheet"

I tried it with:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet").clear();
SpreadsheetApp.openById('ID');
mergeSheet2("SummarySheet", "123");


function mergeSheet2(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.openById('ID');
  var ssd = SpreadsheetApp.openById('ID');
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var lastRow = sourceSheet.getLastRow();
  var lastCol = sourceSheet.getLastColumn();
  var source = sourceSheet.getRange(1,1,lastRow,lastCol); // Error after some iterations: The coordinates or dimensions of the range are invalid. (line 11, file "copy-m.js") -> might be empty spreadsheet
  var destSheet = ssd.getSheetByName(targetSheetName);
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo(destRange, {contentsOnly: true});

Any ideas?

解决方案

The solution is simpler than what you tried, since you want to copy values only and knowing that copyTo only works in the same spreadsheet, just get the values from source and paste it in destination like this:

function copyFromSourceToTarget(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.openById('ID');
  var ssd = SpreadsheetApp.openById('ID');
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var sourceData = sourceSheet.getDataRange().getValues();
  var destSheet = ssd.getSheetByName(targetSheetName);
  destSheet.getRange(destSheet.getLastRow()+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

To make it more flexible I'd recommend to use 4 parameters and simplify again the code :

function copyFromSourceToTarget(targetSheetName,targetSsId, sourceSheetName,sourceSsId) {
  var ss = SpreadsheetApp.openById(sourceSsId)getSheetByName(sourceSheetName);
  var ssd = SpreadsheetApp.openById(targetSsId).getSheetByName(targetSheetName);
  var sourceData = ss.getDataRange().getValues();
  ssd.getRange(ssd.getLastRow()+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

这篇关于从几个电子表格收集数据并将其复制到另一个表单中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！