How to reset DataValidation in Google Sheets when editing a linked Google Form response?

Other Questions Google Sheets
1

I’m using Google Workspace for our office tasks. We have a Google Form for time off requests that feeds into a Google Sheet. I’ve set up some cool stuff with Arrays to match employee names and emails. Now I’m stuck on the Google Apps Script part.

I want to clear the ‘Approval status’ in Column K when an employee cancels their request. This should happen when they use the ‘Edit Response’ link from their confirmation email.

I’ve tried different scripts with onEdit triggers and onFormSubmit functions, but nothing seems to work. Here’s a simplified version of what I’ve attempted:

function onFormSubmit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  let row = sheet.getLastRow();
  let leaveType = sheet.getRange(row, 8).getValue();

  if (leaveType === 'Cancel') {
    sheet.getRange(row, 11).clearContent();
  }
}

I’m not great at coding so I’m really struggling here. Can anyone help me figure out how to reset the DataValidation dropdown in Column K when a cancellation is submitted? Thanks!

2

hey there SoaringEagle, i’ve dealt with similar issues before. have you tried using the onFormSubmit trigger instead of onEdit? it might work better for catching form edits. also, double-check that your script is actually linked to the sheet. sometimes that’s the sneaky culprit! lemme know if you need more help :slight_smile:

3

I’ve worked extensively with Google Forms and Sheets integration, and I can offer some insights. Your approach is on the right track, but there are a few adjustments that might help. First, ensure you’re using the correct event object properties. For form submissions, you should use e.values to access the submitted data. Also, consider using e.range.getRow() instead of getLastRow() to target the specific row that was just updated. Here’s a modified version of your script that might work better:

function onFormSubmit(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var leaveType = e.values[7];  // Assuming leave type is in the 8th column

  if (leaveType === 'Cancel') {
    sheet.getRange(row, 11).clearContent();
    sheet.getRange(row, 11).clearDataValidations();
  }
}

This should clear both the content and data validation in column K when a cancellation is submitted. Remember to set up the trigger correctly in the Script Editor as others have mentioned.

4

I’ve encountered this challenge before while working with Google Sheets and Forms. The solution was to rely on the Form Submit trigger rather than an Edit trigger. You can enable this by opening the Script Editor, navigating to Triggers, and setting up your script to run on form submit events from the spreadsheet. Make sure to grant the necessary permissions by running the script manually at least once. If the issue persists, try logging the event object to see what data is being passed; this can help identify any discrepancies in the expected input.