Unable to submit web app form data to google sheets because “form is unavailable”

I just started getting a “Sorry, unable to open the file at this time” error while trying to submit web app data to google sheets. It hasn’t ever happened before.

Weird thing is, the dropdown values in my form still are being populates from the google sheet columns. Permissions are correct–anyone with a link has access as an editor.

When I execute the code, it runs fine. I suspect it coming from my doGet function, but im not sure.

Code.gs

// Function to handle GET requests
function doGet(e) {
  try {
    var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
    var subs = getDistinctSubstations();
    var url = getUrl();
    htmlOutput.message = '';
    htmlOutput.subs = subs;
    htmlOutput.url = url;
    return htmlOutput.evaluate();
  } catch (error) {
    return HtmlService.createHtmlOutput('Error: ' + error.message);
  }
}

// Function to handle POST requests
function doPost(e) {
  var parcel = e.parameter.parcel.toString();
  var substation = e.parameter.substation.toString();
  var comment = e.parameter.comment.toString();
  var status = e.parameter.status.toString();
  var date = new Date();
  
  // Check if the record already exists based on parcel and status
  if (!isDuplicateStatus(parcel, status)) {
    addRecord(comment, parcel, date);
    addToStatuses(parcel, status, date);
    deleteDuplicateRows(); // Delete duplicate rows after adding the new record
    var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
    var subs = getDistinctSubstations();
    htmlOutput.message = 'Record Added';
    htmlOutput.subs = subs;
    return htmlOutput.evaluate(); 
  } else {
    return ContentService.createTextOutput("Duplicate status submission found. Record not added.");
  }
}

// Function to check for duplicate status submissions
function isDuplicateStatus(parcel, status) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var dataRange = statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn());
  var data = dataRange.getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === parcel && data[i][1] === status) {
      return true; // Found a duplicate status submission
    }
  }

  return false; // No duplicate status submission found
}

// Function to get distinct substations
function getDistinctSubstations() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/");
  var rdSheet = ss.getSheetByName("Raw Data"); 
  var subs = rdSheet.getRange('E2:E').getValues().flat().filter((sub, index, self) => self.indexOf(sub) === index && sub !== "");
  return subs;
}

// Function to get parcels based on substation
function getParcels(substation) {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/");
  var rdSheet = ss.getSheetByName("Raw Data"); 
  var lastRow = rdSheet.getLastRow();
  var substationValues = rdSheet.getRange('E2:E' + lastRow).getValues().flat();
  var parcelValues = rdSheet.getRange('D2:D' + lastRow).getValues().flat();
  var filteredParcels = [];
  
  for (var i = 0; i < substationValues.length; i++) {
    if (substationValues[i] === substation && parcelValues[i]) {
      filteredParcels.push(parcelValues[i]);
    }
  }
  
  return filteredParcels;
}

// Function to get the maximum status for a parcel
function getMaxStatusForParcel(parcel) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var lastRow = statusSheet.getLastRow();
  var parcelValues = statusSheet.getRange('A4:A' + lastRow).getValues().flat();
  var statusValues = statusSheet.getRange('B4:B' + lastRow).getValues().flat();
  var maxStatus = "";

  for (var i = parcelValues.length - 1; i >= 0; i--) {
    if (parcelValues[i] == parcel && statusValues[i]) {
      maxStatus = statusValues[i];
      break;
    }
  }

  return maxStatus;
}

// Function to add a new record to the Comments sheet
function addRecord(comment, parcel, date) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Comments");
  var formattedDate = Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), "MM/dd/yy");
  dataSheet.appendRow([parcel, comment, formattedDate]);
}

function addToStatuses(parcel, status, date) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var isDuplicate = isDuplicateStatus(parcel, status);
  
  // If not a duplicate, append the row
  if (!isDuplicate) {
    var formattedDate = Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), "MM/dd/yy");
    statusSheet.appendRow([parcel, status, formattedDate]);
  }
}

// Function to delete duplicate rows from the Statuses sheet
function deleteDuplicateRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var statusSheet = ss.getSheetByName("Statuses");
  var dataRange = statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn());
  var values = dataRange.getValues();

  // Create an object to store unique rows
  var uniqueRows = {};
  var uniqueData = [];

  // Iterate through the rows and store unique rows in the object
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var key = row.join(",");
    uniqueRows[key] = row;
  }

  // Extract unique rows from the object
  for (var key in uniqueRows) {
    uniqueData.push(uniqueRows[key]);
  }

  // Clear the existing data from row 3 to the last row and write back the uniqueData
  statusSheet.getRange(3, 1, statusSheet.getLastRow() - 2, statusSheet.getLastColumn()).clearContent();
  if (uniqueData.length > 0) {
    statusSheet.getRange(3, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
  }
}

function getUrl() {
  return "https://docs.google.com/spreadsheets/";
}

html:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <title>Comment Submission Form</title>
  <style>
    /* Your CSS styles here */
    body {
      font-size: 15px;
      background-color: #f0f8ff; /* Light blue background color */
    }
    .container {
      width: 50%;
      margin: 0 auto; /* Center the container horizontally */
      text-align: center; /* Center the content inside the container */
      padding-top: 20px; /* Add space at the top */
    }
    h1 {
      margin-top: 20px; /* Add space between header image and header text */
      font-size: 20px;
    }
    select {
      width: 50%; /* Set the width of dropdown menus to fill the container */
      font-size: 12px; /* Set font size for dropdown values */
    }
    textarea {
      height: 100px;
      width: 250px;
      font-size: 12px;
      width: 100%; /* Set font size for textarea */
    }
    input[type="submit"] {
      font-size: 12px; /* Set font size for submit button */
    }
    .submit-message {
      color: red;
      font-size: 12px;
    }
    .landowner-info {
      float: center;
      margin-top: 20px;
      padding: 10px;
      border: 1px solid #ccc;
      border-radius: 5px;
      background-color: #f9f9f9;
      width: 100%; /* Extend size of landowner info box */
    }
  </style>
  <script src="https://apis.google.com/js/api.js"></script>
  <script>
    function updateParcelInfo(parcel) {
      updateMaxStatusForParcel(parcel);
      getLandownerInfo(parcel);
    }

    function getLandownerInfo(parcel) {
      google.script.run.withSuccessHandler(function(landownerInfo) {
        var landownerInfoArray = landownerInfo.split(" (Phone: ");
        document.getElementById("landowner-info-content").innerText = landownerInfoArray[0];
        document.getElementById("phone-info-content").innerText = "Phone: " + landownerInfoArray[1].slice(0, -1); // Remove the closing parenthesis
      }).getLandownerInfo(parcel);
    }

    function getParcels(substation) {
      google.script.run.withSuccessHandler(function(parcels) {
        var parcelDropdown = document.getElementById("parcel");
        parcelDropdown.innerHTML = "";
        parcels.forEach(function(parcel) {
          if (parcel.trim() !== "") {
            var option = document.createElement("option");
            option.value = parcel;
            option.text = parcel;
            parcelDropdown.appendChild(option);
          }
        });

        // Update max status immediately after selecting parcel
        var selectedParcel = parcelDropdown.value;
        updateMaxStatusForParcel(selectedParcel);
        // Fetch landowner info
        getLandownerInfo(selectedParcel);
      }).getParcels(substation);
    }

    function updateMaxStatusForParcel(parcel) {
      google.script.run.withSuccessHandler(function(maxStatus) {
        var statusDropdown = document.getElementById("status");
        statusDropdown.innerHTML = ""; // Clear previous options
        ["", "Outreach", "Negotiating", "Signed"].forEach(function(status) {
          var option = document.createElement("option");
          option.value = status;
          option.text = status;
          if (status === maxStatus) {
            option.selected = true; // Select the option if it matches the max status
          }
          statusDropdown.appendChild(option);
        });
      }).getMaxStatusForParcel(parcel);
    }

    function validateForm() {
      var parcel = document.getElementById("parcel").value;
      var substation = document.getElementById("substation").value;
      var comment = document.getElementById("comment").value;
      var status = document.getElementById("status").value; // Get status value

      if (!parcel || !substation || !comment || !status) {
        document.getElementById("submit-message").innerText = "All fields are required.";
        return false; // Prevent form submission
      }

      // Disable form submit button to prevent resubmission
      document.getElementById("submit-button").disabled = true;

      return true; // Allow form submission
    }

    // Reload the page after form submission to prevent resubmission popup
    window.onload = function() {
      if (window.history.replaceState) {
        window.history.replaceState(null, null, window.location.href);
      }
    };

    // Prevent form resubmission popup on page reload
    if (window.history.replaceState) {
      window.addEventListener("beforeunload", function () {
window.history.replaceState(null, null, window.location.href);
      });
    }
  </script>
</head>
<body>
  <div class="container">
    <img src="https://i.imgur.com/16QsZja.png" alt="Header Image" style="width: 100%; max-width: 200px;">

    <h1>Comment Submission Form</h1>

    <form method="post" action="https://docs.google.com/spreadsheets/" onsubmit="return validateForm()">
      <label>Substation</label><br>
      <select name="substation" id="substation" onchange="getParcels(this.value)">
        <option value=""></option>
        <? var subs = getDistinctSubstations(); ?>
        <? subs.forEach(function(sub) { ?>
          <option value="<?= sub ?>"><?= sub ?></option>
        <? }); ?>
      </select><br><br>

      <label>Parcel</label><br>
      <select name="parcel" id="parcel" onchange="updateParcelInfo(this.value)">
        <!-- Parcel dropdown will be populated dynamically -->
      </select><br><br>

      <label>Status</label><br>
      <select name="status" id="status">
        <!-- Status dropdown will be populated dynamically -->
      </select><br><br>

      <label>Comment</label><br>
      <textarea name="comment" id="comment"></textarea><br><br>

      <input type="submit" name="submitButton" id="submit-button" value="Submit" />
      <span id="submit-message" class="submit-message"></span>
    </form>

    <div class="landowner-info">
      <h2>Landowner Info</h2>
      <p id="landowner-info-content">Loading...</p>
      <p id="phone-info-content"></p>
    </div>
  </div>
</body>
</html>