CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Tayzer Damasceno
Tayzer Damasceno

Reputation: 302

Paste the values for each column until the today's date google apps scripts

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;
  }

enter image description here

Upvotes: 0

Views: 388

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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);
}

Upvotes: 1

Related Questions