Script to copy/rename sheet and copy/modify formula/insert row

Question

I'm hoping this is the place to get some help developing a script to make a few things easier when working with this spreadsheet. The script would basically add a few things to the current sheet. here is how I manually do it now.

So I have a new gamer to add....

Make a new row in sheet 'Summary'. Copy an existing row (doesn't matter which) then paste formulas from that row into new row. Type the new gamer tag into 2nd cell of new row. make duplicate of 'template' sheet'. Rename new sheet to new gamer tag from 2nd cell of new row). now I have to go back to the new row I created in 'summary', change the sheet name in each formula to the new gamer tag sheet and change the cell ref number to 51 because when I copied the formula from another row (cell) it increased the ref cell by 1. example:

=AudioEquation61!D51 from sheet Summary cell D4, copied to sheet summary cell D5 =AudioEquations61!D52. now I change AudioEquations to whatever the new gamer tag is (so it ref the new sheet) and have to change the cell ref D52 to D51.

A few pluses would be to re-alphabetized the gamer tags, have a pull down menu that says "add"...but I can figure the plus out once I get the main script worked out.

A tedious job, especially when there are 20 plus formulas. I have figured out a few things, like making a script to copy a sheet, but I'm afraid that I do not yet have the skills to put all this together in a script. Any help would be greatly appreciated.

Thank you.

edit:

Here is a copy of what I'm working on.....

https://docs.google.com/spreadsheets/d/11T1L67M9fr5wUMozN6fegY6gJhhsnSgNvX80CwniSYY/edit?usp=sharing

List of events -

Ask user for new Gamer Tag - not sure how to code this.
Insert new row above last row in sheet "Master Summary"
Insert new Gamer Tag into cell B of new row.
copy formulas of last row into new row (formulas cell ref are absolute)
make copy of sheet "Blank"
rename "copy of Blank" to new Gamer Tag (maybe pull from cell B of new row?)
change all the formulas in new row of sheet "Master Summary" to ref sheet with new Gamer Tag. can't figure this on out. example =Blank!D$51 change to (what ever new gamer tag is or the value in cell B of new row)!D$51.

All this is simple "by hand" but I'm trying to make it very simple for other users. I'm still learning but some of the coding I got, like inserting a row and making a copy of a sheet but I can't figure out or find any examples on how to rename a sheet to a cell value or change the formula ref.

It sounds like you want someone to write the full script for you.. cYou should start by adding an example sheet to your question and providing the code that you already have. Then people may start to point you in the right direction. — James D

Cooper Cooper · Accepted Answer · 2017-06-19T15:08:56

Here's a simple example of how to get the Gamer Tag from your user.

function getGamerTag()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var ui=SpreadsheetApp.getUi();
  var resp=ui.prompt('Gamer Tag', 'Please enter your Gamer Tag', ui.ButtonSet.OK);
  var gamerTag=resp.getResponseText();
  return gamerTag;
}

Inserting a new row just before the lastRow:

function insertRowBeforeLastRow()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getSheetByName('Master Summary');
  var lastRow=sht.getLastRow();
  sht.insertRowBefore(lastRow);
}

Inserting a new Gamer Tag in cell B of next to last row. This might need some debugging.

function insertNewGamerTag()
{
  var ss=SpreadsheetApp.getActiveSpreadsheet(); 
  var sht=ss.getSheetByName('Master Summary');
  var lastRow=sht.getLastRow();
  var gamerTag=getGamerTag();
  if(gamerTag)
  {
    var rng=sht.getRange(lastRow-1, 2);
    if(String(rng.getValue()).length==0)
    {
       sht.getRange(lastRow-1, 2).setValue(gamerTag);
    }
    else
    {
      SpreadsheetApp.getUi().alert('Cell B of next to last row already has a value.');
    }
  }
  else
  {
    SpreadsheetApp.getUi().alert('No Gamer Tag Provided in insertNewGamerTag()');
  }
}

I'm going to stop for now and give you a chance to implement some of these. Okay here's your function:

function copyBlankSheet() 
{ 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sht = ss.getSheetByName('Blank'); 
  var dest = ss; 
  sht.copyTo(dest); // now rename the sheet 
  var oldname = ss.getSheetByName('Copy of Blank'); 
  oldname.setName('New name'); 
 }

This one got modified to this one:

function copyBlankSheet() 
{ 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sht = ss.getSheetByName('Blank'); 
  var dest = ss; 
  sht.copyTo(dest); // now rename the sheet 
  var oldname = ss.getSheetByName('Copy of Blank'); 
  var newName=ss.getSheetByName('Master Summary').getRange(lastRow-1,2).getValue();
  oldname.setName(newName); 
 }

Script to copy/rename sheet and copy/modify formula/insert row

1 Answers