Create a new Sheet tab and format a header row

The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:

• Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
• Reduce the overall number of columns within the Sheet.
• Insert 1 row of data to use as the Header row.
• Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
  
• Set the Header row background colour, height and column widths.
  

Nicely formatted Header row for appending data to

The Code

The name of the Google Sheet tab you want to look for has been stored as a variable at the top of the script so you can easily tweak it. We start with the usual getting of the spreadsheet and we also try to get said Sheet in the first instance to see if it already exists:

var sheetName = 'SHEET NAME HERE';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getSheet = ss.getSheetByName(sheetName);

Next we perform an if statement to determine our actions based on whether the Sheet exists and if we need to delete the existing before inserting a new blank one:

  if (getSheet === null) {
    // Sheet does not exist, so create it
    var getNewSheet = ss.insertSheet(sheetName);
  }
  else {
    // Sheet already exists so delete and create new one
    ss.deleteSheet(getSheet);
    var getNewSheet = ss.insertSheet(sheetName);
  }

With a new blank Sheet inserted we can look to add our row of data that will become our Header row. Before this I opted to delete some unnecessary columns however as I do not need to scroll across 26 of them. Since they are blank however we need to use something other than the usual 'getLastColumn()' method:

  // get total number of Columns in Sheet regardless of if empty
  var maxCols = getNewSheet.getMaxColumns();

  // delete unnecessary Columns (minus the number we want to keep)
  getNewSheet.deleteColumns(7, maxCols - 6);

  // get Header row range
  var headerRow = getNewSheet.getRange(1, 1, 1, 6);

  // add Header values
  headerRow.setValues([['File Link', 'File ID', 'Type of File', 'Date Created', 'Last Updated', 'Folder Path']]);

Now let's add some styling in there so it looks more like a Header row for data to be appended to:

  // set font size
  headerRow.setFontSize(14);

  // set font colour
  headerRow.setFontColor('white');

  // set font bold
  headerRow.setFontWeight('bold');

  // set font horizontal alignment
  headerRow.setHorizontalAlignment('center');

  // set font vertical alignment
  headerRow.setVerticalAlignment('middle');

  // set row background colour
  headerRow.setBackground('black');

  // set row height
  getNewSheet.setRowHeight(1, 34);

  // set column widths
  getNewSheet.setColumnWidths(1, 6, 208);

And there we have it!

Download

Create a new Sheet tab and format a header row download (please use 'File' > 'Make a copy' for your own version).