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

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.

For a comprehensive set of functions for generating random stuff, see Generating and managing random lists with JavaScript and Apps Script

Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.