Google电子表格 - 脚本，在特定时间将单元格从一张表格复制到另一张表格EVERYDAY [英] Google Spreadsheet - Scripts, copy cell from one sheet to another sheet EVERYDAY at a specific time

问题描述

我知道我可能会问同样的事情。但我尝试了不同答案中的脚本（显然改变了单元格的值），但是我的问题有点不同。

我需要在Google Spreadsheet中创建一个脚本来复制单元格一张纸到另一张纸（在同一份文件中）EVERYDAY在特定的时间。

我在下面附加了图片，显示要复制的单元格。有些单元格在复制之前需要SUM公式。

为了方便起见，我也分享了Google表格的副本。
https://docs.google.com/spreadsheet/ccc ？key = 0AlVSjrM0ckyLdEtiLVNuOVpwQ3BGdUgwU0VpcldKaFE& usp = sharing

图片：
Sheet1和Sheet2
http://i59.tinypic.com/23jsu9x.jpg

解决方案

您可以更新 fromTo 数组以包含更多范围。脚本是不言自明的。设置触发器每天运行 backup 。您可能希望将 GMT 更改为您的时区。

 功能备份）{
 var src = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Daily Report）; 
 var dst = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Monthly Production Report）; 
 var dstRow = getDstRow（dst）; 
 
 var fromTo = [[B26，2]，
 [C26，3]，
 [B29：B31，4]，
 [C29：C31，5]，
 [B34，6]，
 [C34，7]]; 
 
 for（var i = 0; i< fromTo.length; i ++）{
 var r = fromTo [i]; 
 dst.getRange（dstRow，r [1]）。setValue（src.getRange（r [0]）。getValues（）。reduce（sum，0））; 
 
 
 
函数getDstRow（dst）{
 var today = Utilities.formatDate（new Date（），GMT，yyyy-MM-dd ）; 
 Logger.log（今天）; （$ row）; 
 for（var row = 1; row< = dst.getLastRow（）; row ++）{
 Logger.log（dst.getRange（row，1）.getValue（））; 
 
 try {
 var d = Utilities.formatDate（dst.getRange（row，1）.getValue（），GMT，yyyy-MM-dd）; 
 if（d == today）{
 return row; 
} 
} catch（err）{} 
} 
} 
 
函数sum（a，b）{
 return a + b ; 
} 
  

I know i might be asking the same thing. But i tried the scripts in different answers (obviously changing the cell value) but my question is bit different.

I need to make a script in Google Spreadsheet to copy cell from one sheet to another sheet (In the same document) EVERYDAY at a specific time.

I have attached images below showing the what cells to copy from and to. Some cells need a SUM formula before copying.

I have also shared the copy of google sheet for convenience. https://docs.google.com/spreadsheet/ccc?key=0AlVSjrM0ckyLdEtiLVNuOVpwQ3BGdUgwU0VpcldKaFE&usp=sharing

Images: Sheet1 and Sheet2 http://i59.tinypic.com/23jsu9x.jpg

解决方案

You can update the fromTo array to include more ranges. Script is fairly self-explanatory. Set up trigger to run backup everyday. You might want to change GMT to your timezone.

function backup() {
  var src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Report");
  var dst = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly Production Report");
  var dstRow = getDstRow(dst);

  var fromTo = [["B26", 2],
                ["C26", 3],
                ["B29:B31", 4],
                ["C29:C31", 5],
                ["B34", 6],
                ["C34", 7]];

  for (var i=0; i<fromTo.length; i++) {
    var r = fromTo[i];
    dst.getRange(dstRow, r[1]).setValue(src.getRange(r[0]).getValues().reduce(sum, 0));
  }
}

function getDstRow(dst) {
  var today = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
  Logger.log(today);
  for (var row=1; row<=dst.getLastRow(); row++) {
    Logger.log(dst.getRange(row,1).getValue());

    try {
      var d = Utilities.formatDate(dst.getRange(row, 1).getValue(), "GMT", "yyyy-MM-dd");
      if (d == today) {
        return row;
      }
    } catch (err) {}
  }
}

function sum(a,b) {
  return a + b;
}

这篇关于Google电子表格 - 脚本，在特定时间将单元格从一张表格复制到另一张表格EVERYDAY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！