Generating test data for sheets and tables

Sometimes you need to make up some random data for a sheet or a docs table  just for testing in some volume. I use this snippet to generate random data in the right shape. You'll find it in the cUseful library.

Generating test data. 

Can be used like this, to get some random string data of the default size and append it as a table to the current document.

body.appendTable(cUseful.getRandomSheetStrings());

or to a sheet.

var values = cUseful.getRandomSheetStrings();
sheet.getRange(1,1,values.length,values[0].length).setValues(values);

Parameters


The shape of the data can be set by like this

var values = cUseful.getRandomSheetStrings(ROWS,COLUMNS,MIN,MAX);

where

 Parameter  Purpose    
 ROWS How many rows to generate              
 COLUMNS How many columns in each row
 MAX The maximum string length in each cell
 MIN The minimum string length in each cell

The code

/**
 * used to create a random 2 dim set of values for a sheet
 * @param {number} [rows=10] number of rows to generate
 * @param {number} [columns=8] number of columns to generate
 * @param {number} [min=0] minimum number of characeters per cell
 * @param {number} [max=20] maximum number of characters per cell
 * @return {String[][]} values for sheet or docs tabe
 */
function getRandomSheetStrings (rows,columns,min,max) {
  min = typeof min == typeof undefined ?  2 : min;
  max = typeof max == typeof undefined ?  20 : max;
  rows = typeof rows == typeof undefined ?  2 : rows;
  columns = typeof columns == typeof undefined ?  20 : columns;
  
  return new Array(rows).join(',').split(',').map (function() {
    return new Array (columns).join(',').split(',').map(function() {
      var size = Math.floor(Math.random() * (max- min + 1)) + min;
      return size ? new Array(size).join(',').split(',').map(function() {
        return String.fromCharCode(Math.floor(Math.random() * (0x7E - 0x30 + 1)) + 0x30);    
      }).join('') : '';
    });
  });
}

A few notes on Arrays

new Array(SIZE).map 

doesn't work as you'd expect. This is because new Array creates an array of uninitialized values which are ignored by array functions . A workaround for this is to split the array and join it again.

new Array(SIZE).join(',').split(',')

which will create an array whose elements are visible to array functions

The cUseful library

Many of the snippets in this section of the site are part of the cUseful library which you can include in your project. You can find the details below. 

https://script.google.com/macros/s/AKfycbwZ2Hht93wTNzvRmYINYF7obaOHciBXWcP_wAiEtyGq70_x3cI/exec?list=cUseful

The code for cUseful can also  be found on GitHub. For a comprehensive set of functions for generating random stuff, see Generating and managing random lists with JavaScript and Apps Script

For more like this, see Google Apps Scripts snippets. Why not join our forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.




Comments