Is there a way randomly distribute Google Form lead submissions

Question

Is there a way to randomly distribute leads using Google Forms?

A lead enters their information and it's logged in a Google Sheet. The goal is to randomly distribute the leads to other sheets, ideally from the master sheet.

If there isn't a way to allocate directly from the Google Form submission, is there a way to distribute row data from the master sheet to other sheets?

Answer 1

You can do this with the following script attached to the spreadsheet that receives form submissions.

Distributing among sheets within the same spreadsheet

For simplicity, I assume that the submissions go to the first sheet of the spreadsheet (the master), and they should be randomly placed into one of the other sheets. This is what the script below does; to use it, enter it in Tools > Script Editor and add a trigger (Resources > This project's trigger) with parameters "From spreadsheet, on form submit".

function distribute(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = sheets[1+Math.floor(Math.random()*(sheets.length-1))];
  var height = e.range.getHeight()
  var width = e.range.getWidth()
  var lastRow = sheet.getLastRow();
  sheet.insertRowsAfter(lastRow, height);
  e.range.copyTo(sheet.getRange(lastRow+1, 1, height, width));
}

An alternative approach to sheet selection is to have a list of specific sheets to which the content should be placed. If this is preferred, replace the lines with var sheets = ... var sheet = ... by the following:

  var sheets = ['Sheet A', 'Sheet B', 'Sheet C'];
  var sheet = ss.getSheetByName(sheets[Math.floor(Math.random()*sheets.length)]);

---

Distributing among different spreadsheets

Here I'm assuming the data goes to the first sheet of each spreadsheet.

function distribute(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var arrayss = ['Id1', 'Id2', 'Id3'];  // Ids of other spreadsheets
  var targetss = SpreadsheetApp.openById(arrayss[Math.floor(Math.random()*arrayss.length)]);
  var sheet = targetss.getSheets()[0];
  var height = e.range.getHeight()
  var width = e.range.getWidth()
  var lastRow = sheet.getLastRow();
  sheet.insertRowsAfter(lastRow, height);
  sheet.getRange(lastRow+1, 1, height, width).setValues(e.range.getValues());
}