Google Sheets Web App returns success message without adding a row to the Sheet

Question

This Web App stopped working today and I have no idea why. I'm trying to create a Web App that will capture the user's location.

It's returning the successMessage even though it's not adding a new row to the Sheet. I tried to use the exact same code that worked yesterday and I tried all the permissions options for the Web App as well as the Spreadsheet but nothing worked.

Here's a link to the Spreadsheet and it's code: Spreadsheet

This is the HTML script:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-16">
    <title>Google Sheet Form</title>
    <style>
      form {
        display: flex;
        flex-direction: column;
        align-items: center;
        gap: 1rem;
      }

      label {
        font-weight: bold;
      }

      input[type="text"],
      input[type="email"] {
        padding: 0.5rem;
        border: 2px solid gray;
        border-radius: 0.5rem;
        font-size: 2rem;
        width: 100%;
      }

      /* Adjust Get Location button size */
    button[type="button"] {
      padding: 0.5rem;
      border: none;
      border-radius: 0.5rem;
      font-size: 5rem; /* Change this value to adjust the font size */
      background-color: green;
      color: white;
      cursor: pointer;
    }

      button[type="submit"] {
        padding: 0.5rem;
        border: none;
        border-radius: 0.5rem;
        font-size: 5rem;
        background-color: blue;
        color: white;
        cursor: pointer;
      }

      button[type="submit"]:hover {
        background-color: darkblue;
      }
    </style>

    

    <script>
    function getLocation() {
      if (navigator.geolocation) {
        navigator.geolocation.getCurrentPosition(showPosition);
      } else {
        alert("Geolocation is not supported by this browser.");
      }
    }

    function showPosition(position) {
      var latitude = position.coords.latitude;
      var longitude = position.coords.longitude;
      document.getElementById("Latitude").value = latitude;
      document.getElementById("Longitude").value = longitude;
    }

    function showSuccessMessage() {
      const successMessage = document.getElementById('successMessage');
      successMessage.style.display = 'block';
    }

    function sample1(e) {
  const latitude = document.getElementById("Latitude").value;
  const longitude = document.getElementById("Longitude").value;
  if ([latitude, longitude].includes("")) {
    alert("Latitude and/or Longitude are not inputted.");
  } else {
    google.script.run.withFailureHandler(err => console.log(err))
    .withSuccessHandler(f => {
      const responseText = '{"result": "success"}';
      const response = JSON.parse(responseText);
      if (response.result === 'success') {
        showSuccessMessage();
      }
    }).sample2(e);
  }
}
    </script>
  </head>
<body>
  <form>
    <span>Logged In: <?= email ?></span>
    <p> <input size="20" name="Email" type="email" placeholder="Email" required value="<?= email ?>" readonly> </p>
    <p> <input size="20" type="text" id="Latitude" name="Latitude" required readonly> </p>
    <p> <input size="20" type="text" id="Longitude" name="Longitude" placeholder="Longitude" required readonly> </p>
    <p> <button size="20" type="button" onclick="getLocation()">Get Location</button> </p>
    <p> <button size="20" type="submit" onclick="sample1(this.parentNode.parentNode); return false;">Sign In/Sign Out</button>
    </p>

    <!-- Add a div to display the success message -->
    <div id="successMessage" style="display: none;">Form submitted successfully!</div>

    <!-- Add a div to display the error message -->
    <div id="errorMessage" style="display: none;">An error occurred. Please try again.</div>
  </form>
</body>
</html>

And here is the gs script:

const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

function doGet(e) {
    const userEmail = Session.getActiveUser().getEmail();
    var htmlOutput =  HtmlService.createTemplateFromFile('Form');
    htmlOutput.email = userEmail;
    return htmlOutput.evaluate();
}

const sample2 = e => doPost({ parameter: e });

I tried to use the exact same code that worked yesterday and I tried all the permissions options for the Web App as well as the Spreadsheet but nothing worked.

score 0 · Accepted Answer · answered Aug 03 '23 at 11:41

If you are testing your provided Spreadsheet, when I saw your provided Spreadsheet, I noticed that nextRow of "Sheet1" is 1011. And, in your including script works fine using this nextRow. I guessed that this might be the reason for your current issue.

In your situation, how about the following patterns?

Pattern 1:

Please do the following flow.

Manually delete rows after 1000.
Test your script, again.

By this flow, the value of nextRow is 42 in your current Spreadsheet.

Pattern 2:

In this pattern, 1st empty row is detected by a script. Please modify doPost as follows.

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);
  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    const sheet = doc.getSheetByName(sheetName);
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Ref: https://stackoverflow.com/a/44563639
    Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
      const range = this.getRange(offsetRow, columnNumber, 2);
      const values = range.getDisplayValues();
      if (values[0][0] && values[1][0]) {
        return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
      } else if (values[0][0] && !values[1][0]) {
        return offsetRow + 1;
      }
      return offsetRow;
    };
    const nextRow = sheet.get1stEmptyRowFromTop(1);

    const newRow = headers.map(function (header) {
      return header === 'Date' ? new Date() : e.parameter[header];
    });
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

By this modification, the 1st empty row of column "A" is retrieved. And, this value is used as nextRow.

Thank you @Tanaike it's fixed – Pieter le Roux Aug 03 '23 at 16:59 — Pieter le Roux, Aug 03 '23 at 16:59

Google Sheets Web App returns success message without adding a row to the Sheet

1 Answers1

Pattern 1:

Pattern 2: