Google脚本：有条件地将行从一个电子表格复制到另一个 [英] Google Script: Conditionally copy rows from one spreadsheet to another

问题描述

我找到了 https://stackoverflow.com/a/4809413/1438650 的基本解决方案，但它缺乏我的项目的有条件和非连续的方面。我需要该副本基于Col G的值，然后复制列A，B和& H：S到另一个电子表格。

  function movePros（）{
 var date = Utilities.formatDate（new Date，CST，yyyy- MM-dd'T'HH：mm：ss'Z'）//MM / dd / yyyy
 var source = SpreadsheetApp.getActiveSpreadsheet（）; 
 var target = SpreadsheetApp.openById（0ApBdJlRcFy9XdGx0aXRabWlweGxyY3czZzZIemVla3c）; 
 var lastRow = source.getLastRow（）; 
 var sheet = source.getSheetByName（City）; 
 var data = sheet.getDataRange.getValues（）; //这是一个数组，对吧？ 
 Browser.msgBox（data.slice（1））
 //我已经尝试过了，但仍然不知道如何获取单个单元格值，以便我可以检查它们并将它们写入正确的订单不同于源
 var prospect = [DateCalled，PriceSurvey，VolSurvey，Company，Phone，Contact，OdmNotes，Address，City，State，Zip，County，Sic，，，  ， ， ， ， = IFERROR（IF（INDIRECT（\ W\ &安培; ROW（））≤;> \ \，TEXT（INDIRECT（ \ W\ &安培; ROW（）），\ yyyyww\）+ 0，IF（INDIRECT（\ T\ &安培; ROW（））≤;> \\\ \\，TEXT（INDIRECT（\ T\ &安培; ROW（）），\ yyyyww\）+ 0，IF（INDIRECT（\ A\ &安培; ROW（））≤ ;> \ \，TEXT（INDIRECT（\ A\ &安培; ROW（）），\ yyyyww\）+ 1，\ \））），\\ \\ \）， U， ， ， ， ， ，ODM， ， ]; 
 // var prospect = [data（）[0] .....呃，我不知道.... 
 / * 
 var ODM = sheet.getRange（ A + I）.getValues（）; 
 var DateCalled = sheet.getRange（B+ i）.getValues（）; 
 var PriceSurvey = sheet.getRange（H+ i）.getValues（）; 
 var VolSurvey = sheet.getRange（I+ i）.getValues（）; 
 var Company = sheet.getRange（J+ i）.getValues（）; 
 var Phone = sheet.getRange（K1+ i）.getValues（）; 
 var Contact = sheet.getRange（L+ i）.getValues（）; 
 var OdmNotes = sheet.getRange（M+ i）.getValues（）; 
 var Address = sheet.getRange（N+ i）.getValues（）; 
 var City = sheet.getRange（O+ i）.getValues（）; 
 var State = sheet.getRange（P+ i）.getValues（）; 
 var Zip = sheet.getRange（Q+ i）.getValues（）; 
 var County = sheet.getRange（R+ i）.getValues（）; 
 var Sic = sheet.getRange（S+ i）.getValues（）; $（b $ b $ / 
 
 while（i <= lastRow）{
 var i = 2 
 if（sheet.getRange（G+ i）.getValue ）== 1&& sheet.getRange（U+ i）.getValue（）==）{// cheking 
 
 target.appendRow（prospect）; 
 
 source.getRange（U+ i）.setValue（date）; 
 i ++; 
 
} else {
 i ++; 
} 
} 
 
 
} 
  

解决方案

这是一个脚本建议，你可以完成它的工作。
'if'语句需要实现，如果不知道你需要什么，我就做不到。
在这里使用数组的一般想法，我相信你会设法使它工作。

 函数movePros（）{
 var date = Utilities.formatDate（new Date，CST，yyyy-MM-dd'T'HH：mm：ss'Z'）//MM / dd / yyyy 
 var source = SpreadsheetApp.getActiveSpreadsheet（）; 
 var target = SpreadsheetApp.openById（0ApBdJlRcFy9XdGx0aXRabWlweGxyY3czZzZIemVla3c）; 
 var lastRow = source.getLastRow（）; 
 var sheet = source.getSheetByName（City）; 
 var data = sheet.getDataRange（）。getValues（）; 
 var prospect = ['ODM'，'DateCalled'，'PriceSurvey'，'VolSurvey'，'Company'，'Phone'，'Contact'，'OdmNotes'，'Address'，'City，State'， 'Zip'，'County'，'Sic'] 
 var line = new Array（）; 
 var targetArray = new Array（）; 
 targetArray.push（prospect）; //在第一行目标
上创建一个头（i = 0; i< data.length; ++ i）{//迭代行
 for（j = 0; j< data [i] .length; ++ j）{//迭代列
 if（在这里放置您的条件，如果数据必须被复制，您可以有许多条件）{ line.push（data [i] [j]} //如果单元格符合条件，则添加到目标行
} 
 targetArray.push（行）//将行添加到目标（可能需要添加这里也是一个条件）
} 
 target.getRange（1,1，targetArray.length，targetArray [0] .length）.setValues（targetArray）; 
} 
  

备注：data [i]是源表单中的一行，data [i] [j]是此行中的单元格，从那里你可以在任何一行中的任何单元格，通常在for循环中这样做，但你可以做到这一点，而不需要在每一行循环来获取你想要的数据。

<编辑＃1：您复制非连续数据的条件可能如下所示：

  if（j == 5 | | j == 7 || j == 9）{line.push（data [i] [j]）} 
  

抱歉有点长。
编辑＃2：查看您的目标文档我注意到您有多张表单，您还应该告诉脚本应在哪个表单上写入数据。这是一个如何做到的例子：

  var ss = SpreadsheetApp.openById（your spreadsheet ID）; //这已经在脚本中完成了
 SpreadsheetApp.setActiveSpreadsheet（ss）; 
 var sh = SpreadsheetApp.setActiveSheet（ss.getSheets（）[0]）; //选择目标电子表格中的第一个工作表
  

I've found the basic solution https://stackoverflow.com/a/4809413/1438650 but it lacks both the conditional and non-contiguous aspects of my project. I need the the copy to be based on the value of Col G, and then copy columns A, B, & H:S to the other spreadsheet.

function movePros() {
  var date = Utilities.formatDate(new Date, "CST", "yyyy-MM-dd'T'HH:mm:ss'Z'")            //"MM/dd/yyyy"
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0ApBdJlRcFy9XdGx0aXRabWlweGxyY3czZzZIemVla3c");
  var lastRow = source.getLastRow();
  var sheet = source.getSheetByName("City");
 var data = sheet.getDataRange.getValues();  //This makes an array, right?
   Browser.msgBox(data.slice(1))
  // I've tried, but still don't know how get at the individual cell values so that I can both check them and write them in the correct order which is different than the source
  var prospect=[DateCalled,PriceSurvey,VolSurvey,Company,Phone,Contact,OdmNotes,Address,City,State,Zip,County,Sic,"","","","","","","","=IFERROR(IF(INDIRECT(\"W\"&ROW())<>\"\",TEXT(INDIRECT(\"W\"&ROW()),\"yyyyww\")+0,IF(INDIRECT(\"T\"&ROW())<>\"\",TEXT(INDIRECT(\"T\"&ROW()),\"yyyyww\")+0,IF(INDIRECT(\"A\"&ROW())<>\"\",TEXT(INDIRECT(\"A\"&ROW()),\"yyyyww\")+1,\"\"))),\"\")","U","","","","","",ODM,"",""];  
// var prospect=[data()[0]..... ugh, I don/'t know....  
 /*    
var ODM = sheet.getRange("A"+i).getValues();
var DateCalled = sheet.getRange("B"+i).getValues();
var PriceSurvey = sheet.getRange("H"+i).getValues();
var VolSurvey = sheet.getRange("I"+i).getValues();
var Company = sheet.getRange("J"+i).getValues();
var Phone = sheet.getRange("K1"+i).getValues();
var Contact = sheet.getRange("L"+i).getValues();
var OdmNotes = sheet.getRange("M"+i).getValues();
var Address = sheet.getRange("N"+i).getValues();
var City = sheet.getRange("O"+i).getValues();
var State = sheet.getRange("P"+i).getValues();
var Zip = sheet.getRange("Q"+i).getValues();
var County = sheet.getRange("R"+i).getValues();
var Sic = sheet.getRange("S"+i).getValues(); 
 */ 

while (i <= lastRow) {
  var i = 2 
  if (sheet.getRange("G"+i).getValue()==1 && sheet.getRange("U"+i).getValue()=="") {  //cheking 

target.appendRow(prospect); 

source.getRange("U"+i).setValue(date);
i++;

} else {
   i++;
  }     
  } 


}

解决方案

Here is a script "proposal" you could complete to make it work. The 'if' statement needs to be implemented, I couldn't do it without knowing exactly what you needed. The general idea of using arrays it there, I'm sure you'll manage to make it work.

function movePros() {
  var date = Utilities.formatDate(new Date, "CST", "yyyy-MM-dd'T'HH:mm:ss'Z'")            //"MM/dd/yyyy"
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0ApBdJlRcFy9XdGx0aXRabWlweGxyY3czZzZIemVla3c");
  var lastRow = source.getLastRow();
  var sheet = source.getSheetByName("City");
  var data = sheet.getDataRange().getValues();
  var prospect=['ODM','DateCalled','PriceSurvey','VolSurvey','Company','Phone','Contact','OdmNotes','Address','City,State','Zip','County','Sic']
  var line = new Array();
  var targetArray= new Array();
  targetArray.push(prospect);// create a header on first row of target
  for(i=0;i<data.length;++i){ // iterate rows
  for (j=0;j<data[i].length;++j){ // iterate columns
  if (place here your conditions if data must be copied or not, you can have many conditions alltogether){line.push(data[i][j]}// if cell meets condition, add to destination row
  }
  targetArray.push(line)// add row to destination (you could need to add a condition here too)
  }
target.getRange(1,1,targetArray.length,targetArray[0].length).setValues(targetArray);
} 

remark : data[i] is a row of your source sheet, data[i][j] is a cell in this row, from there you can adress any cell in any row. One usually do that in a for loop but you could do it without looping in each row to get the data you want.

Edit#1: your condition to copy non contiguous data could be something like this :

  if (j==5||j==7||j==9){line.push(data[i][j])}

sorry for being a bit long. Edit#2 : looking at your target doc I noticed that you have multiple sheets in it, you should also tell the script on which sheet it should write data. Here is an example of how to do it :

  var ss = SpreadsheetApp.openById("your spreadsheet ID");// this is already done in your script
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sh = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);// select first sheet in target spreadsheet 

这篇关于Google脚本：有条件地将行从一个电子表格复制到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！