是否可以根据该行中的日期编写脚本来复制，粘贴和删除整个行? (Google表格) [英] Is it possible to write a script to copy, paste, and delete entire rows based on a date within that row? (Google Sheets)

问题描述

某些背景:我们有一个共享的Google表格来跟踪我们在电影院的开幕，放映和其他活动.我们有一个主选项卡(主")，其中包含我们所有的事件以及与之相关的详细信息，还有一个用于存档的选项卡(存档").

Some background: We have a shared Google Sheet to track our openings, screenings, and other events at a movie theater. We have a main tab ("Master") that contains all of our events and the details that go with them, and a tab for archiving ("Archive").

我想在Google表格中编写一个脚本来检测事件&根据日期(在E列中)从昨天和更早开始的放映中，选择符合该条件的整行(事件)，将其粘贴到单独的存档"标签，然后从主"标签中删除行.

I would like to write a script within Google Sheets to detect events & screenings that are from yesterday and earlier based on the date (in column E), take the full row(s) (events) that meet that criteria, copy & paste them to the separate "Archive" tab, and then delete the row(s) from the "Master" tab.

任何指向我正确方向的建议都会很有帮助.我发现了一些与此类似的响应，但它们特定于Excel/VBA，对此我并不熟悉(或者很多Javascript).

Anything to point me in the right direction would be super helpful. I found a few similar responses to this but they're specific to Excel/VBA and I'm not familiar with that (or much Javascript, for that matter).

推荐答案

我建议您做一些教程以熟悉如何编写脚本.

I suggested that you do some tutorials to familiarise yourself with how to write scripts.

在这个答案中，我将充实您的代码需要解决的步骤.您会在相同或相似的问题上找到许多现有的主题.这仅仅是为了使您能够更好地搜索所需的代码元素.考虑到这可能只是实现结果的一种方法.

In this answer, I will flesh out the steps that your code needs to address. You will find many existing topics on the same or similar question. This is merely in order to enable you to better search for the elements of code that you need. Consider that this may be just one way of achieving your outcome.

1. 您有一个包含两张纸的电子表格，您将在不同阶段引用这两张纸. getSheetByName(name)将使您能够为工作表创建可重复使用的变量.

1. You have one spreadsheet with two sheets and you will refer to both sheets at different stages. getSheetByName(name) will enable you to create a re-usable variable for a sheet.

您将需要在每张纸的底部找到一行. getLastRow()会有所帮助.

You will need to find the bottom row in each sheet. getLastRow() will help.

您要在主"中找到行，日期，因此您需要获取主"的所有值.
您将首先定义范围-使用getRange(row, column, numRows, numColumns)，尽管这只是定义范围的5种方法之一.

You want to find rows in "Master" for dates, so you need to get ALL values for "Master".
You'll start by defining the range - use getRange(row, column, numRows, numColumns), though this is just one of 5 ways to define a range.

已定义了范围，您将需要"Master"(主)中的值这样您就可以访问日期字段.将getValues()与定义的范围结合使用. FWIW，请注意这是如何复数的，因为其中有很多值.如果只需要一个单元格，则可以使用getValue().

Having defined the range you'll need the values in "Master" so that you can access the date field. Use getValues() in conjunction with the range that defined. FWIW, note how this is in plural because there are lots of values. If you just wanted a single cell, you'd use getValue().

您将要遍历"Master"(主)中的行，并找到日期在今天之前的那些行. 删除电子表格中的重复行"是指删除电子表格中的重复行".本教程显示了一种循环方式，您可以阅读基本的JavaScript循环和迭代".

You'll want to loop through the rows in "Master" and find those rows that have a date prior to today. The "Removing Duplicate Rows in a Spreadsheet" tutorial shows one way of looping, and you can read up on basic JavaScript "Loops and iteration".

在您的情况下，有一个循环的障碍物".如果采用通常"，则进程，那么将从第一行循环到最后一行.但是，您正在从主"中删除一行.并且，随着每行的删除，其余行的行号将/可能会更改；因此，通常"进程不会做.您需要做的两件事:第一)从范围的底部开始循环；第二，从范围的底部开始循环.这将确保其余行的行号永远不会改变；第二)对数据进行排序，以使最早的日期位于底部.因此...现在，您将从下往上循环，并且您将评估所有最旧的日期，而不会遇到遇到的日期大于今天"的风险，就不会有带有日期的其他行的风险少于今天".当然，代码完成后，您可以随时对主"上的数据进行重新排序.返回到您可能希望的任何顺序.

In your scenario, there is a 'hitch' with looping. If one adopts the "usual" process, then one will loop from the first row to the last. However, you are deleting a row from "Master" and, as each row is deleted, the row numbers of the remaining rows will/may change; so the "usual" process won't do. What you need to do is two things: first) loop from the bottom of the range; this will ensure that the row numbers of remaining rows will never change; second) sort the data so that the oldest dates are at the bottom. So... now you will loop from the bottom to the top, and you will evaluate all the oldest dates without any risk that when you encounter a date greater than "today", there will be NO risk of further rows with a date less than "today". Of course, once the code is complete, you can always re-sort the data on "Master" back to any order that you might wish.

您需要比较主"行中的日期带有今天的日期，然后构建if...else语句，以便您可以根据结果定义要执行的操作.比较日期有时说起来容易做起来难.此主题与使用Google Script检查一个日期是否大于另一个日期有关，您可以在其他主题上搜索"Google Sheets Script日期比较".

You need to compare the date in the row in "Master" with today's date and then build a if...else statement so that you can define what to do depending on the result. Comparing dates is sometimes easier said than done. This topic is relevant Checking if one date is greater than the other using Google Script and you can search on other topics for "Google Sheets Script date comparison".

当您发现一个比今天短的日期时，您想要将该行的详细信息复制到"Archive".这是一个两部分的过程，首先是从主"上的行那里收集数据，其次是从主"上的行收集数据.将该数据归档".教程中将介绍如何收集数据.有许多选项可用于将数据复制到存档".您可以追加一行并使用setValues更新新值.替代方案是累积额外的存档"信息.数据并将其添加到归档"循环完成后.

When you find a date less than today, you want to copy the details of that row to "Archive". This is a two part process first) to gather there the data from the row on "Master", and second) to "copy" that data to "Archive". Gathering the data will have been covered in the tutorials. There are many options for copying the data to "Archive". You could append a row and use setValues to update the new values. An alternative is to accumulate the additional "Archive" data and add it to the "Archive" after the loops have been completed.

当您发现一个比今天少的日期时，您想从"Master"中删除该行.有一个命令:deleteRow(rowPosition).

When you find a date less than today, you want to delete the row from "Master". There's a command for that: deleteRow(rowPosition).

您可以按需手动处理功能，也可以选择将其自动化为时间驱动的可安装触发器.该选项是您的.

You can process your function manually, on demand, or you may prefer it to be automated as a time-driven installable trigger. The option is yours.

可以通过多种方式组合这些元素. 在准备上面的摘要时，我必须确保我提供了准确而完整的建议.因此，以下只是实现目标的一种方法.应该注意的是，我的测试数据假设A列和C列的格式分别为日期和时间.

There are many ways that you can combine these elements. In preparing the summary above, I had to make sure that I was providing accurate and complete advice. So the following is but one approach to achieving your goal. It should be noted that my test data assumes that columns A and C are formatted for date and time respectively.

 function so5710086103() {

  // set up spreadsheet and sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var master = ss.getSheetByName("Master");
  var archive = ss.getSheetByName("Archive");

  // get the last row and column of Master
  var masterLR = master.getLastRow();
  var masterLC = master.getLastColumn();
  // get the last row and column of Archive
  var archiveLR = archive.getLastRow();
  var archiveLC = archive.getLastColumn();
  //Logger.log("DEBUG: Last Row - Master = "+masterLR+", and Archive = "+archiveLR);
  //Logger.log("DEBUG: Last Column - Master = "+masterLC+", and Archive = "+archiveLC);

  // create a range, sort it and get the data from "Master"
  var masterRange = master.getRange(2, 1, masterLR - 1, masterLC);
  // sort master based on date
  masterRange.sort({
    column: 1,
    ascending: false
  });
  // Logger.log("DEBUG: Master range = "+masterRange.getA1Notation());
  var masterData = masterRange.getValues();
  //Logger.log("DEBUG: Length of Master data = "+masterData.length);

  // create a range and get the data from "Archive"
  var archiveRange = archive.getRange(1, 1, archiveLR, archiveLC);
  var archiveData = archiveRange.getValues();

  // create a formatted date for today
  var formattedToday = Utilities.formatDate(new(Date), 'GMT+10',
    'dd MMMM yyyy');

  // loop through the rows
  // from bottom to top
  for (var i = (+masterLR - 2); i > 0; i--) {

    // convert cell dates to comparable format
    var DBdate = Utilities.formatDate(masterData[i][0], 'GMT+10',
      'dd MMMM yyyy');
    var DBtime = Utilities.formatDate(masterData[i][2], 'GMT+10',
      'hh:mm a');
    //Logger.log("DEBUG: i = "+i+", DBdate = "+DBdate+", Today = "+formattedToday);

    // clear the temporary row array
    var archivecells = [];

    if (DBdate < formattedToday) {

      // the table date is less than today, so archive the data
      // Logger.log("DEBUG: i = "+i+", DBdate = "+DBdate+", Today = "+formattedToday+" - DB value is less than Today. ACTION: Archive this row");

      // copy the row cells to temporary row array
      archivecells.push(DBdate);
      archivecells.push(masterData[i][1]);
      archivecells.push(DBtime);
      archivecells.push(masterData[i][3]);
      archivecells.push(masterData[i][4]);

      // copy the temporary row array to archivedata
      archiveData.push(archivecells);

      // delete the Master Row
      master.deleteRow(i + 2);

    } else {
      // the table date is NOT less than today, so do nothing
      // Logger.log("DEBUG: i = "+i+", DBdate = "+DBdate+", Today = "+formattedToday+" - DB value is NOT less than Today. ACTION: Do nothing");
    }

    // update the accumulated data to Archive.  
    archive.getRange(1, 1, archiveData.length, archiveLC).setValues(
      archiveData);

  }
}

---

• getSheetByName(name)
• getLastRow()
• getRange(行，列，numRows ，numColumns)
• getValues()
• 循环和迭代
• JavaScript是否...其他
• deleteRow(rowPosition)

---

• getSheetByName(name)
• getLastRow()
• getRange(row, column, numRows, numColumns)
• getValues()
• Loops and iteration
• Javascript if...else
• deleteRow(rowPosition)

主-之前


大师-之后


存档-之后


这篇关于是否可以根据该行中的日期编写脚本来复制，粘贴和删除整个行? (Google表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！