Button to "Archive" rows based on variable

Question

I'm pretty new to Google Sheets and I'm looking for a method/function that will allow a button I create to archive all entries in the spreadsheet to another spreadsheet.

I currently have a column that calculates the current week of the year, and another manually entered column that is for the week of the year that the entry was input. This will eventually be calculated automatically on entry of a new row, but what I'm looking to do is pull all entries from weeks prior to the current week and place those rows into another spreadsheet I dictate.

I know this is possible in Excel, but I'm not the greatest with the programming in Google Sheets, so I'm looking for some advice/help.

EDIT: Quickly I've discovered I'm either REALLY rusty or just not great at Java. I tried the following code:

function myArchive() {
   var aYear = getYear()
   var sourceSheet = SpreadsheetApp.getActive()
     .getSheetByName('Payroll'),
   destSheet = SpreadsheetApp.openById('PayrollArchive')
     .getSheetByName(aYear),
   sourceRanges = ['A:A', 'B:B', 'C:C'],
   targetRanges = [7, 9, 12]
     .map(function (r, i) {
        var sr = sourceSheet.getRange(sourceRanges[i]),
            val = sr.getValues();
        destSheet.getRange(1, r, val.length, val[0].length)
            .setValues(val);
        sr.clear()
    });

 }

Retrieved from here: Is there an easy format to move columns of data by script?

However, I'm trying to do a little different.

I want to copy all entries where column 1 has the week # of the previous week or before. For example, it is currently the 21st week of the year. I want it to pull all entries in the spreadsheet where column 1 is 20 or previous. Not sure how to program the current week in Java.
I want to put ALL information from the rows pulled above into a sheet named the current year in the spreadsheet named PayrollArchive. So, all of the previous weeks' worth of rows would be put in the 2015 sheet in PayrollArchive spreadsheet. I think var aYear = getYear() is the proper coding to grab the current year but is it possible to grab the value of a particular cell in the sheet to get that? I already have the current week # and date information saved in cells within the spreadsheet. The week is saved in J1, and the date is saved in D1:E1 as the last day of the current work week. For example:
```
=TODAY()-WEEKDAY(TODAY(),3)+6
```
What happens when we want to archive the last week of 2015 during the first week of 2016? How do we prevent that 2015 information going into a 2016 sheet.
Will this CREATE the sheet if it does not exist? I currently have a 2015 sheet created, but will I need to manually create a 2016 sheet or will the function create the sheet on its own?

Alan Wells Alan Wells · Accepted Answer · 2015-05-09T15:33:10

To add a custom menu to your spreadsheet, see the documentation:

Google Documentation - Custom Menus

To insert an image, open the help menu:

Help

Do a search on "Insert Image"

You can add a custom menu, or insert an image that is linked to a script. There probably is a function or combination of functions that you could put into the spreadsheet to archive that would get data from the first spreadsheet, then you could copy all the formulas to values. That method would use formulas, then copy the formulas to values. It can all be done in code also, but you'd need to write a script to do that. So it's possible.

See posts like these:

Google Script to copy specific columns from one sheet to another sheet

Move columns of data by script

Copy from one sheet to another sheet

Button to "Archive" rows based on variable

1 Answers