Google Sheets script to copy/paste from one sheet to another

1.6k Views Asked by At

Could someone help me with creating a script that does the following:

Function: Upon adding a new sheet to my already existing workbook, I would like it to copy Column "E" from Sheet 1 and (Paste Special > Conditional formatting only) to the newly introduced Sheet "X"

1

There are 1 best solutions below

0
On

Where can I learn more on how to write code? I have never used Stackoverflow by the way someone just recommended me to come here. I believe I just answered my own question somehow on the post, which I am sure was wrong to do but I couldn't comment on an already existing answer without exceeding the limit.

// Adds custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('CustomMenu').addItem('Copy format', 'copyFormat') //Add function to menu.'GIVE NAME HERE', 'functionName' 

    .addToUi();  

}


function copyFormat() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sh1 = ss.getSheets()[0]; //Gets the first sheet of the workbook. Can use ss.getSheetByName('Name of sheet here'); If it is not the first sheet
  var activeSh = ss.getActiveSheet(); //Get the active sheet, you should be on the sheet just added
  var rowEnd = activeSh.getLastRow(); //Last row of active sheet


  sh1.getRange("E1:E").copyFormatToRange(activeSh, 5, 5, 1, rowEnd); //Copy format, including conditional, to column E of active sheet


}

This just adds a button that allows you to select a cell and give it the same conditions has in the original sheet.

Example: Sheet 1: Column E1:E100 has a given condition.. which needs to apply in the exact same way to any new incoming sheets since they all come in the same format. Right now its at a point of which when a new sheet arrives in the workbook: I can enter the new sheet > Select the cell which requires the conditions > Select the custom menu > Cell gets conditioned. So the next step would be automate the process I just mentioned since there is several sheets added daily to the workbook.