I am working on a Lunch sheet Project for the school I work at & I need to copy data each month based on lunch & breakfast that is reimbursable. I have the sheets working but I need to copy & paste the data to another sheet so I can collect all the data for the month. I have a script working to Copy & Paste the data but I need it to only copy & paste the data for the 2 rows that corresponds with today's date & not the whole page.

This is a link to the helper copy of the sheet I made:

https://docs.google.com/spreadsheets/d/1cp8Y36tlzq9n4_7jX1QDhYV3AeF3MT-OO1GeZLR3uwM/edit#gid=28052480

If you can help it would be greatly appreciated.

This is the code I tried but it doesn't only do the 2 columns that have the Date:

I just need it to Copy & Paste the 2 Columns "Lunch" & "Breakfast" based on today's date in Row 2 & do it on a time trigger at 3:20 PM CST US time every day.

This was as far as I got, I need it to roll with today's date every day, "N7" was the starting point but I don't know how to get it to only copy & paste the 2 columns under today's date every day.

function runsies() {
  var ss =  SpreadsheetApp.openById("1cp8Y36tlzq9n4_7jX1QDhYV3AeF3MT-OO1GeZLR3uwM");
  var sheet   = ss.getSheetByName("Nutrition Data");
  var rows = sheet.getDataRange().getValues();
  var dates = rows[1];
  var column;
  var today = new Date();
  for (var i = 15; i < dates.length; i  ) {
    if (dates[i].getDate() == today.getDate() && dates[i].getMonth() == today.getMonth()) {
      column = i   2;
      break;
    }
  }
  var sheet2 = ss.getSheetByName("Copy of Nutrition Data");
  sheet.getRange(7,14,sheet.getLastRow(), column).copyTo(sheet2.getRange("N7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

CodePudding user response：

Try this:

function runsies() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Nutrition Data');
  const targetSheet = ss.getSheetByName('Copy of Nutrition Data');
  const timezone = ss.getSpreadsheetTimeZone();
  const dateStrings = sheet.getRange('A2:2')
    .getValues()
    .flat()
    .map(date => {
      if (Object.prototype.toString.call(date) === '[object Date]') {
        return Utilities.formatDate(date, timezone, 'yyyy-MM-dd')
      }
    });
  const todayString = Utilities.formatDate(new Date(), timezone, 'yyyy-MM-dd');
  const columnIndex = dateStrings.indexOf(todayString)   1;
  if (!columnIndex) {
    throw new Error(`Cannot find today's date (${todayString}).`);
  }
  sheet.getRange(7, columnIndex, sheet.getLastRow() - 7   1, 2)
    .copyTo(targetSheet.getRange(7, columnIndex), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}