3

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?

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());
}
  • This looks fantastic. Many thanks. Is this passing the data to another sheet in the same document? If so is there a way to pass the data to a sheet in an entirely different document? – dfgross81 Feb 17 '16 at 14:07
  • Yes, within the same spreadsheet. You said "to other sheets", not "to other spreadsheets". To transfer data to other spreadsheets, collect their Id strings into array, like the second version of the script. Use openById to open them. You'll also need to know the names of sheets within those spreadsheets where the data should go. Otherwise, it's same as above. – user79865 Feb 17 '16 at 14:21
  • Thanks Sally. I apologize for not clarifying in my initial question. Can you please provide the script? I am not sure how to incorporate openById(id) into the second version of the script. – dfgross81 Feb 17 '16 at 16:53
  • Thanks Sally! I updated the script, adding the other spreadsheet's ID. When I run the script, I get this error: TypeError: Cannot read property "range" from undefined. (line 6, file "Distribute") I tested it out a couple times and it didn't work. Anything I am doing wrong here? Many thanks! I sincerely appreciate your help. – dfgross81 Feb 22 '16 at 21:52
  • @dfgross81 The script is not meant to be run manually. It should be called by a trigger on form submit, as described in my answer. – user79865 Feb 22 '16 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.