Google Apps脚本-将包含今天日期的工作表中的行复制到另一工作表中 [英] Google Apps Script - Copy rows from one sheet containing today's date into another

问题描述

我要做的是在一个电子表格中，查看工作表源"，如果B列中的单元格包含今天的日期，则在A:B之间复制该单元格的单元格，然后粘贴到工作表中的下一个空行中目的地".

What I'm trying to do is within one spreadsheet, look at sheet "Source", if a cell in column B contains today's date, then copy cells between A:B for that cell and paste onto next empty row in sheet "Destination".

重复所有包含今天日期的单元格.

Repeat for all cells containing today's date.

如果我正在寻找文本，这是可行的，但实际上，我试图在日期更改时每天自动运行它，而我无法使date函数正常工作.

This works if I'm looking for a text but essentially I'm trying to make this run automatically everyday when the date changes and I can't get the date function to work properly.

基本上，每天工作之初，它都会从另一张自动工作表中复制今天的数据，然后将其从昨天的数据粘贴到另一行中的下一行.

Essentially at the turn of each day it would copy today's data from another automated sheet, and paste it in the next row down from yesterday's data in another sheet.

我对所有这些都是新手，任何帮助将不胜感激.

I'm quite new to all of this and any help would be greatly appreciated.

function copy_test() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source"); //Source sheet
  var ds = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination"); //Destination sheet
  var testrange = ss.getRange('B:B'); //Column to check for today's date
  var testvalue = (testrange.getValues()); //What value to check
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy');
  var data = [];
  var j = [];

  //Condition to check in B:B, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,ss.getRange(i+1,1,1,3).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  ds.getRange(ds.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

我得到的错误是:

TypeError:无法从未定义中读取属性"length".(第20行，文件首次测试")

TypeError: Cannot read property "length" from undefined. (line 20, file "First Test")

我要复制的数据类型的图片

推荐答案

以防将来有人遇到此问题.我弄乱了原始代码，并设法使其正常工作:

In case anyone comes across this problem in the future. I messed around with the original code some more and managed to get it to work:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Source'); //source sheet
  var testrange = sheet.getRange('B:B');
  var testvalue = (testrange.setNumberFormat("@").getValues());
  var ds = ss.getSheetByName('Destination'); //destination sheet
  var data = [];
  var j =[];
  var dt = new Date();
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy')

  //Condition to check in B:B, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,sheet.getRange(i+1,1,1,3).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  ds.getRange(ds.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

特别是 var testvalue ，在其中我在 setNumberFormat 中添加了日期作为文本，因为这是它适用于 if(testvalue [i] ==今天)

Specifically var testvalue where I added in setNumberFormat for the dates as text because that's the only way it would work for if (testvalue[i] == today)

感谢@Cooper对此一枪.

Thanks @Cooper for taking a shot at this.

这篇关于Google Apps脚本-将包含今天日期的工作表中的行复制到另一工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！