粘贴每列的值，直到今天的Google Apps脚本为止 [英] Paste the values for each column until the today's date google apps scripts

问题描述

我有这张表，其中一些信息与其他表链接，几乎所有行都有公式(您可以在图片上找到).

我更新其他选项卡上的信息，并使用公式将值和信息粘贴到行中，但是我每天都在更新，因此我需要将值复制/粘贴到该选项卡中直到今天为止.选择列，然后粘贴值.

就像图片上所示，从7月2日(列)开始，我有了公式，只有值之前的几天，我希望脚本将信息粘贴到今天./p>

这是脚本，我开始打开电子表格，然后转到选项卡，获取值的范围.

日期代表我拥有日期的第5行，而Todaydate代表今天的日期.

我的问题是遍历每一列并粘贴值.我想将A列中的值粘贴到具有今天日期的列中.

我发现这部分是" spreadsheet.getRange('').copyTo(spreadsheet.getActiveRange()，SpreadsheetApp.CopyPasteType.PASTE_VALUES，false)"我认为可以用来复制/粘贴值，但是我不使用数字就无法访问不同的列.

在循环中，我考虑了第5行上的日期，并将其与今天的日期进行比较.

  var sheet = SpreadsheetApp.openById("...");var ss = sheet.getSheetByName("Reporting");var rows = ss.getDataRange().getValues();变量日期=行[5]今天的var =新的Date();var todaydate =今天；Logger.log(列)for(var i = 1; i< dates.lenght; i ++){如果(dates [i] == todaydate)休息;} 

解决方案

正确比较日期很重要

• new Date()将为您提供一个具有日期和时间戳的日期对象，因为工作表中日期的时间戳默认为 00:00:000 ，您需要将这一部分排除在比较之外.
• 为此，您可以例如仅将日期和月份与 getDate()进行比较和 getMonth()
• 找到正确的日期后，请使用其索引查找正确的列
• 您的代码有一些小错误，例如索引编制-请记住，数组索引始终以 0 开头，而不是以 1 开头！

修改后的示例:

  function myFunction(){var ss = SpreadsheetApp.openById("...");var sheet = ss.getSheetByName("Reporting");var rows = sheet.getDataRange().getValues();//数组以[0]开头-表格中的第5行对应于rows [4]var date = rows [4];var列；今天的var =新的Date();for(var i = 0; i< dates.length; i ++){如果(dates [i] .getDate()== today.getDate()&& dates [i] .getMonth()== today.getMonth()){列= i + 1;休息;}}var sheet2 = ss.getSheetByName("Sheet2");//修改"A1"到您的实际开始范围sheet.getRange(1,1，sheet.getLastRow()，列).copyTo(sheet2.getRange("A1")，SpreadsheetApp.CopyPasteType.PASTE_VALUES，false);} 

I've this table, with some information that are link with other tables, almost all the rows have formulas (you can find on the picture).

I update the information on the other tabs and with the formulas, the values and information are pasted in the rows, but I update day by day, so I need to copy/paste the values until the today's day inside this tabs instead of select the columns, and paste values.

Like is shown on the picture, from day July 2nd (column) I've the formulas, and the days before I've only values, I'd like to have the script pasting the information til the today's day.

Here is the script, that I started to opening the spreadsheet, then going to the tab, taking the range of values.

The dates represents the row 5 where I've the dates and the todaydate represents the today's date.

My problem is to go through each column and paste the values. I'd like to paste the values from column A til the column that have the today's date.

I found this part "spreadsheet.getRange(' ').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)" that I think is possible to use to copy/paste the values, but I don't how can I access different columns without using the numbers.

Inside the loop, I take in consideration the dates on the row 5 and compare with the today's date to break it.

  var sheet =  SpreadsheetApp.openById("...");
  var ss    = sheet.getSheetByName("Reporting");
  var rows = ss.getDataRange().getValues();
  var dates = rows[5]
    
  var today = new Date();
  var todaydate = today;
  Logger.log(columns)
  
  
  for(var i = 1; i < dates.lenght; i++) {
  if (dates[i]==todaydate)
  break;
  }

解决方案

It is important to compare dates correctly

• new Date() will give you a date object that has a date AND timestamp, since the timestamp of your dates in the sheet is by default 00:00:000, you need to take this part out of the comparison.
• For this purpose you can e.g. compare only the day and month with getDate() and getMonth()
• Once you find the correct date, use its index for finding the right column
• Your code has some minor mistakes such as indexation - keep in mind that array indices always start with 0, not with 1!

Modified sample:

function myFunction() {
  var ss =  SpreadsheetApp.openById("...");
  var sheet    = ss.getSheetByName("Reporting");
  var rows = sheet.getDataRange().getValues();
  //arrays start with [0] - the 5th row in the sheet corresponds to rows[4]
  var dates = rows[4];
  var column;
  var today = new Date();

  for(var i = 0; i < dates.length; i++) {
    if (dates[i].getDate() == today.getDate() && dates[i].getMonth() == today.getMonth() ){
      column = i+1;
      break;
    }
  }
  var sheet2 = ss.getSheetByName("Sheet2");
    //modify "A1" to your actual start range
  sheet.getRange(1,1,sheet.getLastRow(), column).copyTo(sheet2.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

这篇关于粘贴每列的值，直到今天的Google Apps脚本为止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！