Creating a Script in google apps script to launch a google form

Question

I am trying to create a script where if you change the values in Column D from blank to "Create Something", it will launch a google form.

I tried this code, but nothing happens. Also edited the trigger button, but nothing happens.

function onEdit(e) {
  var sheet = e.source.getSheetByName('Sheet1'); // Replace 'YourSheetName' with the actual name of your sheet
  var columnDIndex = 15; // Column D is the 4th column (1-indexed)

  if (e.range.getColumn() == columnDIndex && e.value == 'Create Something') {
    openGoogleForm();
  }
}

function openGoogleForm() {
  var formUrl = 'https://docs.google.com/forms/d/yourid'; // Replace 'your-form-id' with the actual ID of your form
  var form = FormApp.openByUrl(formUrl);
  form.createResponse().submit();
}

Answer 1

I believe your goal is as follows.

• When a value of Create Something is put into column "D" of "Sheet1", you want to open Google Form.

Modification points:

• I think that var columnDIndex = 15; // Column D is the 4th column (1-indexed) and e.range.getColumn() == columnDIndex mean column "O".
• In the case of e.value == 'Create Something', when a value of Create Something is put by copying and pasting, the value of e.value has no value. Please be careful about this.
• In order to open HTML, it is required to use the installable OnEdit trigger instead of the simple trigger.

When these points are reflected in a sample script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. And, please install the OnEdit trigger to the function installedOnEdit. Ref

And, please set your Google Form URL to formUrl.

And, please confirm your sheet name. In this sample, when a value of Create Something is put into column "D" of "Sheet1", the script works. Please be careful about this.

function installedOnEdit(e) {
  var { range, value } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && !range.isBlank()) { // or if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && (value == 'Create Something' || range.getValue() == 'Create Something')) {
    var formUrl = 'https://docs.google.com/forms/d/{formId}/edit'; // Please set your Google Form URL.
    var form = FormApp.openByUrl(formUrl);
    form.getPublishedUrl();
    var script = `<script>window.open('${form.getPublishedUrl()}', '_blank');google.script.host.close();</script>`;
    var html = HtmlService.createHtmlOutput(script);
    SpreadsheetApp.getUi().showModalDialog(html, 'sample');
  }
}

• When you put a value of Create Something into column "D" of "Sheet1", a Google Form is opened.

• In this sample, in order to open Google Form, Javascript is used on a dialog.

• In this sample, the Google Form of the public URL is opened. If you want to directly open Google Form please modify the above script as follows.

  function installedOnEdit(e) {
    var { range, value } = e;
    var sheet = range.getSheet();
    if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && !range.isBlank()) { // or if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && (value == 'Create Something' || range.getValue() == 'Create Something')) {
      var formUrl = 'https://docs.google.com/forms/d/{formId}/edit'; // Please set your Google Form URL.
      var script = `<script>window.open('${formUrl}', '_blank');google.script.host.close();</script>`;
      var html = HtmlService.createHtmlOutput(script);
      SpreadsheetApp.getUi().showModalDialog(html, 'sample');
    }
  }
  

• If you don't want to check the sheet name, please remove sheet.getSheetName() == "Sheet1" && .

References:

• Installable Triggers
• showModalDialog(userInterface, title)

Added:

From your provided Spreadsheet, I noticed that your value of var formUrl = 'https://docs.google.com/forms/d/yourid'; is the public URL. From your script, I thought that this URL might be the URL for editing.

In this case, it is required to use my 2nd script. But, from your situation, I'm worried about the installable trigger. So, I added one more sample script including your URL. Please confirm it.

1. Please replace your current script with the following script.

2. Please directly run installOnEditTrigger() with the script editor. By this, the installable OnEdit trigger is installed to the function installedOnEdit.

3. Please edit a cell of column "D" of "Sheet1". By this, the script is automatically run by the installable OnEdit trigger.

function installedOnEdit(e) {
  var { range, value } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && !range.isBlank()) { // or if (sheet.getSheetName() == "Sheet1" && range.columnStart == 4 && (value == 'Create Something' || range.getValue() == 'Create Something')) {
    var formUrl = 'https://docs.google.com/forms/d/e/###/viewform'; // Please set your Google Form URL.
    var script = `<script>window.open('${formUrl}', '_blank');google.script.host.close();</script>`;
    var html = HtmlService.createHtmlOutput(script);
    SpreadsheetApp.getUi().showModalDialog(html, 'sample');
  }
}

// Please run this function. By this, the installable trigger is installed to the function "installedOnEdit".
function installOnEditTrigger() {
  const functionName = "installedOnEdit";
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(t);
    }
  });
  ScriptApp.newTrigger(functionName).forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}