Data randomizer for data analytics

In early 2017 I posted a tutorial on the creation of randomized HR “data-sets” for use in statistical models, or simply to test your skills at analyzing HR related data in Excel.

If you want to learn how to generate your own randomized HR data in Excel, in order to test statistical models or theories or just to get accustom to working with HR related data, you can follow along in the tutorial below.  I will show you how to make use of the “=randbetween” and “=vlookup” functions in Excel in order to achieve this outcome.” – People Analytics – Randomized HR Data with “=randbetween” and “=vlookup”

Since then, I’ve found myself moving more and more towards Google Sheets (for multiple reasons, but the main reason being its support of JavaScript – in the form of App Script).

I wanted to find away of creating randomized data-sets without using “=randbetween” or “=vlookup’s” and on top of that, a way to execute the creation of these data-sets with the click of a single button.

I therefore had two options, learn VBA or rely on my existing JavaScript “skills” in order to find a solution….the answer was a no-brainer.

Below is a code extract from the project I’m currently busy with (in Google Sheets). You are welcome to copy the code below into one of your own projects and play around.

Copy the code, then go to “Google Sheets –> Tools –> Script editor… “, delete the existing code and paste the code below. Give the script a name, save the script and refresh your sheet.

You should see an extra menu item (called “Generate Data”) that was added to the ribbon (top menu in Google Sheets).

To run the script click: “Generate Data –> Generate Data – Append” and watch the magic happen.

Ps. This is not the final product 😉

Have fun…


/**************************************************************** Menu Setup ***************************************************************/

// Add quick acces menu

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Generate Data').addItem('Generate Data - Append', 'generateDataAppend').addToUi();

function onInstall(){

/**************************************************************** Spreadsheets Setup *******************************************************/

// Create random values based on variable input values specified in 'generateDataAppend'
function randomizer(e){
  var randomize = Math.floor(Math.random()*Math.floor(e));
  return randomize;

// Declare global values
var race                = ['African','Indian','Coloured','White'],
    gender              = ['Male','Female'],
    department          = ['HR','Finance','Logistics','IT','Operations','Sales','Legal','Communications'],
    nationality         = ['South African','Foreign National'],
    employment_type     = ['Permanent','Contract','Fixed-Term'],
    occupational_level  = ['Top management','Senior management','Professionally qualified and experienced specialists and mid-management',
                           'Skilled technical and academically qualified workers\, junior management\, supervisors\, foremen\, and superintendents', 'Semi-skilled and discretionary decision making',
                           'Unskilled and defined decision making'];

// Generate random data and append to last row in active sheet
// Remember, arrays start at 0 not 1
function generateDataAppend() {
  for (var i=0; i<200; i++){
    var race_random       = this.randomizer(4), 
        gender_random     = this.randomizer(2),
        department_random = this.randomizer(8),
        national_random   = this.randomizer(2), 
        employment_random = this.randomizer(3), 
        occupation_random = this.randomizer(6),
        sheet             = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([race[race_random],gender[gender_random],department[department_random],nationality[national_random],

Data randomizer for data analytics

Until next time…

Leave a Reply

Your email address will not be published. Required fields are marked *