Google Spreadsheet - How to create sheet on the air, using SpreadsheetApp and Utilities class services

urls-create-sheet-ota.gs

/**
 * This script has been especially made for a particular context.
 * So use it with caution, and do not forget to customize it before any use.
 */

//globals
var sheet  = SpreadsheetApp.getActiveSpreadsheet(),
    sheets = sheet.getSheets();

/**
 * Return the human month name.
 */
function getHumanMonth(month)
{
  var months = {
    "01": "January",
    "02": "February",
    "03": "March",
    "04": "April",
    "05": "May",
    "06": "June",
    "07": "July",
    "08": "August",
    "09": "September",
    "10": "October",
    "11": "November",
    "12": "December",
  };

  //check if exists
  if (months[month] === undefined) {
    return months["01"];
  }

  return months[month];
}

/**
 * Update sheets.
 *
 * For a better integration, use the onOpen listener function.
 * Works with this example of tabs:
 * [RESUME] • [2017-03] • [2017-02] • [2017-01] • [...]
 */
function main()
{
  //get vars
  var name = sheets[1].getName(),
      date = Utilities.formatDate(new Date(), "GMT", "yyyy-MM");

  //check current date and latest created tab
  if (name == date) {
    //vars
    var parts = date.split("-");

    //works on dates
    var day   = new Date(parseInt(parts[0], 10), parseInt(parts[1], 10)),
        month = day.getMonth() + 1,
        year  = day.getFullYear();

    //works on month
    month = 10 > month ? "0"+month : month;

    //enable latest interesting sheet
    if (2 <= sheets.length) {
      sheet.setActiveSheet(sheets[1]);
    }

    //create a new sheet
    var newsheet = sheet.duplicateActiveSheet();
    newsheet.activate();
    newsheet.setName(year+"-"+month);

    //move sheet
    sheet.moveActiveSheet(2);

    //clear content (considering from the 2nd line to H column) and set cell's value
    newsheet.getRange("A2:H").clearContent();
    newsheet.getRange("A1").setValue(getHumanMonth(month));
    newsheet.getRange("B1").setValue(year);

    //flush
    SpreadsheetApp.flush();
  }
}