Format and tidy a Google Sheet report

The following Google Apps Script is designed to take a Microsoft Excel file, convert it to a Google Sheet, remove unnecessary data/columns/rows, add some nice formatting and concatenation to create email addresses. The purpose of this is to show you some nice and easy ways of working with spreadsheet data, as well as creating something that will become a time-saver if you regularly do this manually.

Use Google Apps Script to easily format a Report

The Code

There are lots of comments in the code itself, I do want to draw out a few of the chosen methods however:

• 'deleteRows()' - we use the plural method here so that we can specify the exact starting row and then how many we wish to delete.
• 'deleteColumns()' - we use the plural method here so that we can specify the exact starting column and then how many we wish to delete.
• 'insertColumnAfter()' - we use the singular method here as we only want to add one column after the position we specify.
• 'insertRows()' - we use the singular method here as we only want to add one row after the position we specify.
• Header row formatting - a link to my previous blog post that will break this step down a bit more in terms of font size, colour, weight, etc.
• Autofill Sheet formula - previous blog post that will break down how we can add the concatenate formula to create the email address for each row.
  

Download

Format and tidy a Google Sheet report folder download (please use 'File' > 'Make a copy' for your own version of each item).