Copy Data with function to a New Sheet

Copy Data with function to a New Sheet

The purpose of this code is to add a custom menu to a Google Sheet that allows the user to copy the values of the currently selected range and paste them into a new sheet with a unique name. When the user clicks on the custom menu item, it runs the copyFun function, which retrieves the active spreadsheet, active sheet, and active range. It then passes the values from the active range to the createMySheet function, which creates a new sheet with a unique name and sets the values from the active range in the original sheet to the new sheet.

function onOpen(){

  const ui = SpreadsheetApp.getUi();

  ui.createMenu(‘Adv’)

  .addItem(‘copy’,’copyFun’)

  .addToUi();

}

This function adds a custom menu to the Google Sheet. When the user clicks on the menu, it will run the copyFun function.

function copyFun(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getActiveSheet();

  const range = sheet.getActiveRange();

  const data = range.getValues();

  createMySheet(data,ss,range);

  //const ui = SpreadsheetApp.getUi();

  //ui.alert(data);

}

This function retrieves the active spreadsheet, active sheet, and active range. It then gets the values from the active range and calls the createMySheet function with those values as an argument.

function createMySheet(data,ss,range){

  const numSheets = ss.getSheets();

  const sheetName = ‘Sheet ‘+numSheets.length;

  let newSheet = ss.getSheetByName(sheetName);

  if(newSheet == null){

    newSheet = ss.insertSheet();

    newSheet.setName(sheetName);

  }else{

    newSheet.clear();

  }

  const newRange = newSheet.getRange(1,1,range.getNumRows(),range.getNumColumns());

  newRange.setValues(data);

}

This function creates a new sheet with a unique name, either by inserting a new sheet or clearing an existing sheet. It then retrieves the range of the new sheet and sets the values from the active range in the original sheet to the new sheet.

function copyMe(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getActiveSheet();

  const range = sheet.getActiveRange();

  const data = range.getValues();

  createASheet(data,ss,range);

  range.setBackground(‘red’);

}

function createASheet(data,ss,range){

  const numSheets = ss.getSheets();

  const sheetName = ‘Sheet ‘+ numSheets.length;

  let newSheet = ss.getSheetByName(sheetName);

  if(newSheet == null){

    newSheet = ss.insertSheet();

    newSheet.setName(sheetName);

  }else{

    //newSheet.clearContents();

    //newSheet.clearFormats();

    newSheet.clear();

  }

  const newRange = newSheet.getRange(1,1,range.getNumRows(),range.getNumColumns());

  newRange.setValues(data);

}

The copyMe() function does the following:

1. Gets the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet().
2. Gets the active sheet using ss.getActiveSheet().
3. Gets the active range using sheet.getActiveRange().
4. Gets the values in the active range using range.getValues().
5. Calls the createASheet() function passing the data, spreadsheet, and range as arguments.
6. Sets the background color of the active range to red.

The createASheet() function does the following:

1. Gets the number of sheets in the spreadsheet using ss.getSheets().
2. Generates a name for the new sheet by appending the number of sheets to ‘Sheet ‘.
3. Tries to get a sheet with the generated name using ss.getSheetByName().
4. If the sheet does not exist, it inserts a new sheet using ss.insertSheet(), sets the name of the sheet to the generated name, and stores the reference to the new sheet in the newSheet variable.
5. If the sheet exists, it clears the contents and formatting of the existing sheet using newSheet.clear().
6. Gets the range of the new sheet using newSheet.getRange() with the same dimensions as the original range.
7. Sets the values of the new range to the data from the original range using newRange.setValues().

Overall, the copyMe() function takes the active range, creates a new sheet in the same spreadsheet, and copies the data from the active range into the new sheet. It also highlights the original range in red. The createASheet() function is used to create a new sheet and copy the data to it.