Collate and organise a large Sheet of data

The following Google Apps Script is designed to organise a large Google Sheet of data into a specific category and then populate a Google Sheet template file with it. In this example our main data consists of these columns:

• URLs
• Last Update Time
• Creator Email
• Owner Email
• Incompatibility Reason

We want to organise the jumbled data so that we gather together all the rows of the same Owner Email address and pop these into a separate Google Sheet, which is then shared ('edit' access). Repeating this process for the next unique Owner Email address.

Collate and organise a Sheet of data by Owner Email

This blog post is a working example of a previous one I wrote entitled 'Organise files into a JavaScript Object 2.0' which implements the same underlying technique and goes into a little bit more detail than here.

Guidance

A lot of the techniques used in this code can be found elsewhere on my blog, in addition to the usual comments I include in my code.

Once you have made a copy of the Google Sheet files you will need to open your version of 'Collate and organise large Sheet of data'. The 'Data' tab is where the raw data sits that needs organising. If you go to Extensions > Apps Script > '2) createSheets' on the left-hand side > enter your destination folder ID for where you want the new (organised) files to be created and your template file ID. Remember to save your changes.

The Function file '1) collateData' in the Apps Script Editor is what sorts through the Sheet data. Here we use arrays to match column positions to pick out the exact values we require. If you add/remove/change columns then you will need to make adjustments to this code yourself.

Download

Collate and organise a large Sheet of data folder download (please use 'File' > 'Make a copy' for your own version).