CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
Mike - SMT
Mike - SMT

Reputation: 15226

When form data is added to row automatically generate a drop down list in row

So I have a function that will get the form event submission and move specific fields to a separate worksheet. Now I need to be able to add a drop down menu to the first row that contains some options IE.("NEW", "COMPLETE", "PENDING"). Is there a method for adding a drop down list to a cell via the function?

Here is my working code:

function AM2(e) {
  var form_name = "Form Responses 1";
  var open_name = "OPEN";

  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var form_ws = workbook.getSheetByName(form_name);
  var open_ws = workbook.getSheetByName(open_name);

  open_ws.appendRow(['',e.values[1],'',e.values[2]]);
}

Based on what I found I thought I could use SpreadsheetApp.newDataValidation().requireValueInList(['NEW', 'COMPLETE', 'PENDING'], true).

I was thinking that I could do something like this:

function AM2(e) {
  var form_name = "Form Responses 1";
  var open_name = "OPEN";

  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var form_ws = workbook.getSheetByName(form_name);
  var open_ws = workbook.getSheetByName(open_name);

  open_ws.appendRow(open_ws.appendRow([SpreadsheetApp.newDataValidation().requireValueInList(['NEW', 'COMPLETE', 'PENDING'], true).build(),e.values[1],'',e.values[2]]);
}

But this does not work and causes an error. I cannot find any documentation that tells me how I can add a drop down at the same time I am appending rows to a sheet.

Maybe there is a way to grab the range of appendRow(). If I could get this I can probably work around this error.

Any thoughts?

Upvotes: 1

Views: 807

Answers (1)

ross
ross

Reputation: 2774

Issue:

You won't be able to do this as part of appendRow(). You need to use setDataValidation() to achieve this, which has to be used on a Range object.

Solution:

Add the data validation rule straight after you append your row.

var row = open_ws.appendRow(['NEW',e.values[1],'',e.values[2]]).getActiveRange().getRow();

//set data validation
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['NEW', 'COMPLETE', 'PENDING'], true).build();
open_ws.getRange(row, 1).setDataValidation(rule);

As you can see, I've used your code to build the validation rule in var rule, then all we need to do is get the range of your newly-appended row using getActiveRange().getRow() on the append line and then set the validation using setDataValidation(rule).

Reference:

Upvotes: 1

Related Questions