Tuesday, June 25, 2013

Google Spreadsheet Script How To Auto Rename Sheet

Question:

I have a sheet consisting of a master sheet and some other sheets. My master sheet is connected to the others so that I can retrieve the information of the sheets in my master.

Here is my question:
Whenever I add a row to my master sheet I want two things to happen:

1- it adds a new sheet calling it QC26 (for example if the latest sheet is QC25 I want that it names this sheet as QC26 and the next one QC27 and so on...) 
2-Copy the formula in the above row to this row considering that the information from the above row comes from sheet QC25 and the one for this example has to be the same cells but from sheet QC26 and so on.

I already know how to duplicate the sheet with the following script but I cannot change its name automatically.

function create()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = ss.getSheetByName("Template");
  ss.insertSheet("QC2", 1, {template:temp});
}

Can anyone help me with this?


Solution:

Try the following Script:

function create()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var temp = ss.getSheetByName("Template");
  
  var sheets =  ss.getSheets();
  var sheetname = sheets[sheets.length-1].getSheetName();
  
  var oldname = String(sheetname).substring(2,4);
  var temp_increment = parseInt(oldname)+1;
  var newname = String(sheetname).substring(0,2)+temp_increment;
  //Browser.msgBox(newname);
  ss.insertSheet(newname, sheets.length, {template:temp});
}

Note:

You must have latest Sheet for example "QC30" at the right most side, that is at the right end side of the sheets panel.

The above script will take the name of the last sheet and then it will take the substring of the name and then rename it and insert the new sheet at the last position.

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

1 comment:

  1. thanks for your help I dont have access to my sheet tonight but I'll give it a try tomorrow n will let you know. by the way have you any idear about the second part of my question too?

    ReplyDelete