Apps Script web application not updating Google Sheets data when user makes edits

Other Questions Google Sheets
1

I’m building a web app using Google Apps Script that displays data from my spreadsheet. Users should be able to edit certain fields directly in the web interface, and these changes need to be saved back to the original Google Sheet.

The problem is that when users make edits through the web app, the data doesn’t get updated in the spreadsheet. I’m using a doPost function to handle the updates, but something isn’t working correctly.

Here’s my current code:

function doGet() {
  var targetSheet = "MainData";
  var worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet);
  var allData = worksheet.getDataRange().getValues();

  var currentDate = new Date();
  currentDate.setHours(0, 0, 0, 0);

  var matchingRows = allData.filter((record, idx) => idx !== 0 && record[3] === "pending");
  matchingRows.sort((x, y) => new Date(y[5]) - new Date(x[5]));

  var htmlContent = `
    <h1>Data Management</h1>
    <table border="1" style="width: 100%; border-collapse: collapse;">
      <tr>
        <th>Contact</th>
        <th>Status</th>
        <th>Notes</th>
        <th>Active</th>
      </tr>
  `;

  matchingRows.forEach((record, idx) => {
    var contactInfo = record[1].toString();
    var isActive = record[15] === true;

    htmlContent += `
      <tr>
        <td>${contactInfo}</td>
        <td contenteditable='true' onBlur='modifyData(${idx + 2}, 3, this.innerText)'>${record[2]}</td>
        <td contenteditable='true' onBlur='modifyData(${idx + 2}, 16, this.innerText)'>${record[14]}</td>
        <td><input type='checkbox' ${isActive ? "checked" : ""} onclick='modifyData(${idx + 2}, 16, this.checked)'></td>
      </tr>
    `;
  });

  htmlContent += `</table>
    <script>
      function modifyData(rowNum, colNum, newValue) {
        fetch(ScriptApp.getService().getUrl(), {
          method: 'POST',
          body: JSON.stringify({ rowNum, colNum, newValue }),
          headers: { 'Content-Type': 'application/json' }
        });
      }
    </script>`;

  return HtmlService.createHtmlOutput(htmlContent);
}

function doPost(request) {
  try {
    var requestData = JSON.parse(request.postData.contents);
    var worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MainData");
    
    if (requestData.rowNum && requestData.colNum !== undefined) {
      worksheet.getRange(requestData.rowNum, requestData.colNum).setValue(requestData.newValue);
      return HtmlService.createHtmlOutput("Update successful");
    }
  } catch (err) {
    Logger.log("Update failed: " + err);
    return HtmlService.createHtmlOutput("Update failed");
  }
}

What am I missing to make the data updates work properly?

2

your doPost function isn’t getting called at all. the problem is fetch() doesn’t work with apps script web apps - you need to use google.script.run instead. also, you’re filtering the data but then using the wrong row numbers when updating. store the original row index as data attributes in your html so you can reference the correct sheet position later.

3

Your column references are messed up - there’s a mismatch between what you’re showing and what you’re updating. You’re displaying record[2] for status in the HTML but calling modifyData with column 3. Same thing with notes - you show record[14] but reference column 16 in the update call.

I ran into this exact issue last year building something similar. The row indexing gets completely screwed because you filter the data first, then try to map back to the original sheet positions.

What fixed it for me: store the actual sheet row number as a data attribute in each table row when you generate the HTML. Then use that instead of the filtered index. Also agree that google.script.run is the way to go for client-server communication in Apps Script web apps.

4

Your POST request setup is broken. You can’t use fetch() with ScriptApp.getService().getUrl() because ScriptApp only works server-side, not in client JavaScript. Use google.script.run instead to call your server function directly. Try google.script.run.doPost({rowNum, colNum, newValue}). Also, your row indexing is messed up. You’re filtering data but still using filtered index + 2 for the actual sheet row - that’ll update the wrong cells. Pass the original row number from your filtered data, not the filtered index.