Can I use a script to copy and rename a sheet in Google Sheets with the current data as the tab name?

user_14670845 0
Viewed 309

I have a data sheet that will update weekly. I would like a script to copy the sheet and rename it with the current date. The goal is to preserve the data in the sheet weekly before it updates so that I have a record of the data. This is the script that I have so far:

    /** @OnlyCurrentDoc */function myFunction() {
    let sh, temp_one_values;
    sh = SpreadsheetApp.getActive();
    temp_one_values = transpose_(sh.getSheetByName('Complete Tutoring Roster').getRange('A1:Z1000').getValues());
        {temp_one.copyTo(sh).setName(szToday);
 
    }
}

function transpose_(array) {
    return Object.keys(array[0]).map(function (col) {
        return array.map(function (row) {
            return row[col];
        });
    });
}

function CopyDataSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.duplicateActiveSheet();
  spreadsheet.getActiveSheet().setName('11/19/2020');
};

I used record a macro to get the script.

I know that I need to edit the setName code. But I am not sure how to code it to pull the current date.

google-apps-script google-sheets
1 Answers

Explanation:

  • You just need to get the date of today in the desired format: MM/dd/yyyy:

    const today = Utilities.formatDate(new Date(), spreadsheet.getSpreadsheetTimeZone(), "MM/dd/yyyy");

  • And then use the copyTo(spreadsheet) method to duplicate the sheet into the existing spreadsheet and then name it with the date of today:

    sheet.copyTo(spreadsheet).setName(today);

  • You don't need to activate ranges or sheet. That is a logic generated by macros but it makes your code confusing and inefficient. Instead of getting the active sheet, use getSheetByName to get the desired sheet based on its name.

Solution:

In this solution, change Sheet1 to the name of the sheet you want to duplicate.

function CopyDataSheet() {
 const spreadsheet = SpreadsheetApp.getActive();
 const sheet = spreadsheet.getSheetByName('Sheet1'); 
 const today = Utilities.formatDate(new Date(), spreadsheet.getSpreadsheetTimeZone(), "MM/dd/yyyy");
 sheet.copyTo(spreadsheet).setName(today);
};
user_112252910
Related