(Google API) Dynamic Sheet and Name of Sheet Creation Script from Cell Reference

Question

I need help with my functions, which should:

Copy hidden sheet
Ensure no duplicate named sheets exists
Dynamically name the copy from cell reference
Show copy of sheet

Here is what I have so far, but I am not too clear with the syntaxes: Fix: (Incorrect '=' instead of '==' on if statements)

    function checkSheetName(name)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  var cSheets = ss.getNumSheets();

  for(i = 1; i < cSheets; i++)
  {
  var currentSheetName = allSheets[i].getName();
  if(currentSheetName == name);
  return true;
  }
  return false;
}

function createNextMonth()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = ss.getSpreadsheetTimeZone();
  var allSheets = ss.getSheets();
  var dateSheet = ss.getSheetByName("Start Date:");
  var currentSheet = ss.getActiveSheet();
  var startRange;

  // Check where to get the cell references from
  if(dateSheet != currentSheet)
    startRange = currentSheet.getRange(4,7);
  else
    startRange = currentSheet.getRange(4,7);

  var startDate = new Date(startRange.getValue());
    // Format date to MONTH (YYYY)
  var newSheetName = Utilities.formatDate(startDate, tz, "yyyy-MMMMM-dd'T'HH:mm:ss'Z'");
  if(!newSheetName)
    // Error making name
    return false;

  if(!checkSheetName(newSheetName))
    // Error duplicate
    // Inform user
    return false;

    // Locate the template sheet
  var templateSheet = ss.getSheetByName("TEMPLATE");
  if(!templateSheet)
    // Error missing
    // Couldn't find template
    return false;

    // Set the active sheet to the template sheet
  ss.setActiveSheet(templateSheet);
    // Duplicate active sheet
  ss.duplicateActiveSheet();

  // Unsure how to locate duplicated sheet

  ss.renameActiveSheet(newSheetName);

  // Unsure how to set new sheet cell B3 to startDate

  return true;
}

Update: Forgot to mention the problem is that it seems to not be getting either a correct date value, or getting the right cell.

Update 2: Corrected simple syntax mistake on loop which fixed problem.

Tiana Tiana · Accepted Answer · 2016-03-19T10:33:18

Here would be a good start r.e. checking for a duplicate sheet and setting the new copy as active (credit https://ctrlq.org/code/19973-duplicate-sheet-google-spreadsheets) :

function cloneGoogleSheet() {

  var name = "labnol";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Template').copyTo(ss);

  /* Before cloning the sheet, delete any previous copy */
  var old = ss.getSheetByName(name);
  if (old) ss.deleteSheet(old); // or old.setName(new Name);

  SpreadsheetApp.flush(); // Utilities.sleep(2000);
  sheet.setName(company);

  /* Make the new sheet active */
  ss.setActiveSheet(sheet);

}

Something like this might help with using a cell value as name:

  var bb = ss.getSheetByName("sheetnamehere");
  var newsheetname = bb.getRange("j6");
  var newsheetvalue = newsheetname.getValue();
  sheet.setname(newsheetvalue)

(Google API) Dynamic Sheet and Name of Sheet Creation Script from Cell Reference

1 Answers