将更新后的特定数据导入多个电子表格 [英] Import specific data from renewed to multiple spreadsheets

问题描述

虽然我相信它应该在这里，但我找不到解决方案。也许，我用来搜索的关键字不正确。

问题：

我想从电子表格表中导入（捕获）引用区域A的数据，名为新数据。该表每2个工作日更新一次。

https://docs.google.com/spreadsheets/d/1WRFvZ5XXpf06WPgC70KRtGi5_hmGKZXFiFYcW806_uk/edit?usp=drivesdk ）

和将这些新行添加到名为区域A的电子表格中，该区域已拥有自己的数据。

https ：//docs.google.com/spreadsheets/d/1c_SBGjUNOr2Oi2tpCVlmD0e1x1Ov52khJYN5jcm8Mf8/edit？usp = drivesdk

我试过的：

导入范围功能很好，但还不够。我必须打开Region A Spreadsheet并在表格末尾写入该函数，然后将结果复制粘贴到6个区域的所有Spreadsheet文档中，如示例中（在现实生活中大约为50）。然后在2天内从新数据表中获取renewe数据并复制它。

有没有一种方法可以应用脚本来添加行（来自New数据到区域A表）解决问题？

谢谢！

更新26.12.2016：请看到我的评论如下，我无法解决一个问题

解决方案

这有点不清楚，但它听起来像你要查询什么每个区域的数据，所以对于区域A尝试：

  = query（IMPORTRANGE（1WRFvZ5XXpf06WPgC70KRtGi5_hmGZZFFiFYcW806_uk，Sheet Name！A1 ：D），Col1包含'地区A'）
  

只需更改工作表名称！以上

编辑＃2

根据要求不追加行的注释：

  function regionA（）{
 //数据表
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var dataSheet = ss.getSheets（）[0]; 
 var data = dataSheet.getDataRange（）。getValues（）; 
 
 //代码区域A 
 var regionAId =
 var regionA =区域A; 
 var regionAdata = SpreadsheetApp.openById（regionAId）.getSheets（）[0]; 
 var column = regionAdata.getRange（'A2：A'）; 
 var values = column.getValues（）; 
 var ct = 1; 
 while（values [ct]&& amp; values [ct] [0]！=）{
 ct ++; 
} 
 
 for（var i = 0; i< data.length; i ++）{
 if（data [i] [0] == regionA）{
 regionAdata.getRange（ct + 2,1,1,4）.setValues（[data [i] .slice（0，4）]）; 
 ct ++ 
} 
} 
} 
  

编辑＃1

添加一行

根据您的评论，您可以使用此脚本在你的newData文件中而不是你的报告文件。

  function myFunction（）{
 //数据表
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var dataSheet = ss.getSheets（）[0]; 
 var data = dataSheet.getDataRange（）。getValues（）; 
 
 //您可以为每个区域更改这些区域
 var regionAId =区域A的电子表格键
 var regionA =区域A; 
 var regionAdata = SpreadsheetApp.openById（regionAId）; 
 var regionBId =区域B的电子表格键
 var regionB =区域B; 
 var regionBdata = SpreadsheetApp.openById（regionBId）; 
 //添加其他区域
 
 for（var i = 0; i< data.length; i ++）{
 if（data [i] [0] == （data [i] [0] == regionB）{
 regionBdata.appendRow（data [i]）{
 regionAdata.appendRow（data [i]）
} 
 ]）
} 
 //添加其他区域
} 
} 
  

这并不是那么干净，因为我很快做到了，但按照模式添加了其他区域，一切都将一次完成。

I have searched through, but I can't find the solution, although I believe it should be here. Perhaps, keywords I am using to search are not correct.

Problem:

I want to import (capture) data that refers to Region A from spreadsheet table named "New Data". The table is renewed every 2 work days.

https://docs.google.com/spreadsheets/d/1WRFvZ5XXpf06WPgC70KRtGi5_hmGKZXFiFYcW806_uk/edit?usp=drivesdk)

and add these new row to spreadsheet named Region A which already has it's own data.

https://docs.google.com/spreadsheets/d/1c_SBGjUNOr2Oi2tpCVlmD0e1x1Ov52khJYN5jcm8Mf8/edit?usp=drivesdk

What I tried:

Importrange function works good, but it is not enough. I have to open the Region A Spreadsheet and write the function at the end of the table, then copy paste the results also for all Spreadsheet documents of 6 Regions as in the example (in real life the are around 50). And then in 2 days to grab the renewe data from "New Data" table and copypaste it.

Is there a way to apply a script that would add rows (from New Data to Region A table) to solve the problem?

Thank you!

Update 26.12.2016: Please see my comments below, I can't solve one problem

解决方案

It is a bit unclear but it sounds like you what to query the data for each region, so for region A try:

 =query(IMPORTRANGE("1WRFvZ5XXpf06WPgC70KRtGi5_hmGKZXFiFYcW806_uk","Sheet Name!A1:D"),"where Col1 contains 'Region A'")

just change Sheet Name! above

EDIT #2

Based on comments asking not to append row:

function regionA() {
  // Data Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var data = dataSheet.getDataRange().getValues();

  //Code for Region A
  var regionAId = ""
  var regionA = "Region A";
  var regionAdata = SpreadsheetApp.openById(regionAId).getSheets()[0];
  var column = regionAdata.getRange('A2:A');
  var values = column.getValues(); 
  var ct = 1;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }

  for ( var i=0; i < data.length; i++) {
    if (data[i][0] == regionA){
      regionAdata.getRange(ct+2,1,1,4).setValues([data[i].slice(0, 4)]);
      ct++
    } 
  }
}

EDIT #1

Appending a row

Based on your comments you can use this script in your newData file not your report files.

  function myFunction() {
  // Data Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var data = dataSheet.getDataRange().getValues();

  //You can change these for each Region
  var regionAId = "SPREADSHEET KEY for RegionA"
  var regionA = "Region A";
  var regionAdata = SpreadsheetApp.openById(regionAId);
  var regionBId = "SPREADSHEET KEY for RegionB"
  var regionB = "Region B";
  var regionBdata = SpreadsheetApp.openById(regionBId);
  //add the other regions

  for ( var i=0; i < data.length; i++) {
    if (data[i][0] == regionA){
      regionAdata.appendRow(data[i])
    }     
    if (data[i][0] == regionB){
      regionBdata.appendRow(data[i])
    }    
    //add the other regions
  }
}

It's not that clean because I did it quickly but follow the pattern to add the other regions and everything will be done at once.

这篇关于将更新后的特定数据导入多个电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！