Copy a tab to new tab within the same sheet using onEdit

Question

Is it possible to make a script that could make a copy of a tab to new one with in the same sheet? If so, is there someone that could help me?

The reason for this is because I have this sheet with all my prospects and when a booked meeting is going to be executed I then want a new copy of a tab that has this meeting template to be created so I can use the new copy to manage my meeting and keep notes.

Now I have to use the same tab with the template and then erase it when a new meeting is held or just make a copy of the tab but I want to automate it. I also have formulas in the original script that I want to copied as well to the new copy

I have added a example sheet and when a new meeting is being held I put in a date in Column L to mark when it got executed and when that happens I want a new copy of the tab to be created.

https://docs.google.com/spreadsheets/d/1ZBM-fpRYu8Xw_FiXfb03jTAn1_FRnDW2lkSHGH7AmTk/edit#gid=1000209132

I have tried to search if someone else had the same idea and issue but havent found it

@Cooper Sorrry, now there is a question there – Eric Morandeau Mar 09 '23 at 06:23 — Eric Morandeau, Mar 09 '23 at 06:23
That's two questions and the answer to the first is yes. – Cooper Mar 09 '23 at 14:32 — Cooper, Mar 09 '23 at 14:32

score 0 · Accepted Answer · answered Mar 09 '23 at 07:50

I found a script that works perfectly for this and I post here for the community if someone else needs help with this kind of challenge.

Here is the script:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  makeCopyOfTemplate_(e);
}

/**
* Makes a copy of a template sheet when a value is hand entered in
* a blank cell in a magic column.
*
* Does not create new sheets when existing values in the magic column
* are edited.
*
* @param {Object} e The onEdit() event object.
*/
function makeCopyOfTemplate_(e) {
  // version 1.0, written by --Hyde, 8 March 2023
  //  - see https://support.google.com/docs/thread/205234084
  const magic = {
    sheet: /^(Pipe)$/i,
    column: 13, // column M
    templateSheetName: 'Meeting template',
  };
  try {
    let newSheetName;
    if (e.oldValue
      || e.range.columnStart !== magic.column
      || !e.range.getSheet().getName().match(magic.sheet)
      || !(newSheetName = e.value || e.range.getDisplayValue())) {
      return;
    }
    const template = e.source.getSheetByName(magic.templateSheetName);
    if (!template) {
      throw new Error(`Cannot find a template sheet named '${magic.templateSheetName}'.`)
    }
    const newSheet = template.copyTo(e.source).activate();
    newSheet.setName(newSheetName);
    e.source.toast(`Created new sheet '${newSheetName}.`, 'makeCopyOfTemplate_');
  } catch (error) {
    e.source.toast(error.message, 'makeCopyOfTemplate_', 30);
    throw error;
  }
}
 
Paste the code in Extensions > Apps Script, completely replacing the placeholder code there. Then save and close the script editor. The script will run automatically when you manually edit column Pipe!M1:M.

Copy a tab to new tab within the same sheet using onEdit

1 Answers1