How to save changes from a web app to Google Sheets using Apps Script? - Stack Overflow

admin管理员组

文章数量:1288048

In Google Sheets, I want to take certain columns from a table and create an app through deployment. I need the user to be able to edit these fields, and all changes should be reflected in the original table. I’m trying to implement this using doPost, where sheet.getRange(row, col).setValue(value) updates the cell in the table.

However, this doesn’t work, and the changes are not applied to the original sheet.

What can I do to make this work?

// Required function for publishing Google script app
function doGet() {
  var sheetName = "Sheet1";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();

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

  var filteredData = data.filter((row, index) => index !== 0 && row[2] === "/");
  filteredData.sort((a, b) => new Date(b[4]) - new Date(a[4]));

  var latestDate = new Date(filteredData[0]?.[4]);
  latestDate.setHours(0, 0, 0, 0);

  var htmlOutput = `
    <h2>Filtered Rows</h2>
    <table style="width: 100%; border-collapse: collapse; font-family: Arial, sans-serif;">
      <tr>
        <th style="padding: 10px; border: 1px solid #ddd;">Phone</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Name</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Date</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Comment</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Not a Lead</th>
      </tr>
  `;

  filteredData.forEach((row, index) => {
    var date = new Date(row[4]);
    date.setHours(0, 0, 0, 0);
    var formattedDate = `${date.getDate()}.${date.getMonth() + 1}.${date.getFullYear()}`;
    var phoneNumber = row[0].toString().replace(/^(7)/, "+7");
    var isNotLead = row[21] === true;

    htmlOutput += `
      <tr>
        <td style="padding: 10px; border: 1px solid #ddd;"><a href='tel:${phoneNumber}'>${phoneNumber}</a></td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 2, this.innerText)'>${row[1]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;">${formattedDate}</td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 21, this.innerText)'>${row[20]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;"><input type='checkbox' ${isNotLead ? "checked" : ""} onclick='updateCell(${index + 2}, 22, this.checked)'></td>
      </tr>
    `;
  });

  htmlOutput += `</table>
    <button onclick="saveChanges()">Save Changes</button>
    <script>
      function updateCell(row, col, value) {
        fetch('YOUR_APP_URL', {
          method: 'POST',
          body: JSON.stringify({ row, col, value }),
          headers: { 'Content-Type': 'application/json' }
        }).then(response => response.text()).then(console.log);
      }

      function saveChanges() {
        alert('Changes saved!');
      }
    </script>`;

  return HtmlService.createHtmlOutput(htmlOutput);
}

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    if (data.row && data.col !== undefined) {
      sheet.getRange(data.row, data.col).setValue(data.value);
      return HtmlService.createHtmlOutput("Cell updated");
    }
  } catch (error) {
    Logger.log("Error: " + error);
    return HtmlService.createHtmlOutput("Data update error");
  }
}

In Google Sheets, I want to take certain columns from a table and create an app through deployment. I need the user to be able to edit these fields, and all changes should be reflected in the original table. I’m trying to implement this using doPost, where sheet.getRange(row, col).setValue(value) updates the cell in the table.

However, this doesn’t work, and the changes are not applied to the original sheet.

What can I do to make this work?

// Required function for publishing Google script app
function doGet() {
  var sheetName = "Sheet1";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();

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

  var filteredData = data.filter((row, index) => index !== 0 && row[2] === "/");
  filteredData.sort((a, b) => new Date(b[4]) - new Date(a[4]));

  var latestDate = new Date(filteredData[0]?.[4]);
  latestDate.setHours(0, 0, 0, 0);

  var htmlOutput = `
    <h2>Filtered Rows</h2>
    <table style="width: 100%; border-collapse: collapse; font-family: Arial, sans-serif;">
      <tr>
        <th style="padding: 10px; border: 1px solid #ddd;">Phone</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Name</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Date</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Comment</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Not a Lead</th>
      </tr>
  `;

  filteredData.forEach((row, index) => {
    var date = new Date(row[4]);
    date.setHours(0, 0, 0, 0);
    var formattedDate = `${date.getDate()}.${date.getMonth() + 1}.${date.getFullYear()}`;
    var phoneNumber = row[0].toString().replace(/^(7)/, "+7");
    var isNotLead = row[21] === true;

    htmlOutput += `
      <tr>
        <td style="padding: 10px; border: 1px solid #ddd;"><a href='tel:${phoneNumber}'>${phoneNumber}</a></td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 2, this.innerText)'>${row[1]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;">${formattedDate}</td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 21, this.innerText)'>${row[20]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;"><input type='checkbox' ${isNotLead ? "checked" : ""} onclick='updateCell(${index + 2}, 22, this.checked)'></td>
      </tr>
    `;
  });

  htmlOutput += `</table>
    <button onclick="saveChanges()">Save Changes</button>
    <script>
      function updateCell(row, col, value) {
        fetch('YOUR_APP_URL', {
          method: 'POST',
          body: JSON.stringify({ row, col, value }),
          headers: { 'Content-Type': 'application/json' }
        }).then(response => response.text()).then(console.log);
      }

      function saveChanges() {
        alert('Changes saved!');
      }
    </script>`;

  return HtmlService.createHtmlOutput(htmlOutput);
}

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    if (data.row && data.col !== undefined) {
      sheet.getRange(data.row, data.col).setValue(data.value);
      return HtmlService.createHtmlOutput("Cell updated");
    }
  } catch (error) {
    Logger.log("Error: " + error);
    return HtmlService.createHtmlOutput("Data update error");
  }
}

• google-sheets
• google-apps-script
• web-applications

Share Improve this question Follow asked Feb 22 at 15:10 Alexandr VolbergAlexandr Volberg 111 silver badge 5

• 3 @Tedinoz WebApps help: "...the following subjects are considered off-topic here: Creating or developing web applications" – doubleunary Commented Feb 23 at 6:45
• "this doesn’t work" — doesn't work how? Please show the exact error message you get, including error.stack. Add console.log(e) as first thing in doPost(e). See Cloud logging and How to debug small programs. – doubleunary Commented Feb 23 at 6:57
• Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide markdown table. or an anonymous sample spreadsheet (using this link). – Lime Husky Commented Feb 23 at 11:35
• To have a specific update cell you need an identifier such as unique id that represents its whole set of data. – Lime Husky Commented Feb 23 at 12:40
• @doubleunary Thank you. I had the wrong definition of a "web app" :) (and I up-ticked your comment) – Tedinoz Commented Feb 24 at 7:05

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

The OP code throws the following error message in the web browser console

Access to fetch at '<DEPLOYMENT WEB APP URL>' from origin '<IFRAME WEB APP URL>' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

The preflight request is caused using headers: { 'Content-Type': 'application/json' }. Replace this with headers: { 'Content-Type': 'text/plain' }.

Also, I suggest using the ContentService instead of the HtmlService in the doPost function.

Below is the OP code with a few changes

1. Use global variables for the web app URL and the sheet name.
2. The abovementioned changes.

const URL = "<WEB APP URL>";
const SHEET_NAME = "<Name of the sheet holding the data>";

// Required function for publishing Google script app
function doGet() {
  var sheetName = SHEET_NAME;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();

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

  var filteredData = data.filter((row, index) => index !== 0 && row[2] === "/");
  filteredData.sort((a, b) => new Date(b[4]) - new Date(a[4]));

  var latestDate = new Date(filteredData[0]?.[4]);
  latestDate.setHours(0, 0, 0, 0);

  var htmlOutput = `
    <h2>Filtered Rows</h2>
    <table style="width: 100%; border-collapse: collapse; font-family: Arial, sans-serif;">
      <tr>
        <th style="padding: 10px; border: 1px solid #ddd;">Phone</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Name</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Date</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Comment</th>
        <th style="padding: 10px; border: 1px solid #ddd;">Not a Lead</th>
      </tr>
  `;

  filteredData.forEach((row, index) => {
    var date = new Date(row[4]);
    date.setHours(0, 0, 0, 0);
    var formattedDate = `${date.getDate()}.${date.getMonth() + 1}.${date.getFullYear()}`;
    var phoneNumber = row[0].toString().replace(/^(7)/, "+7");
    var isNotLead = row[21] === true;

    htmlOutput += `
      <tr>
        <td style="padding: 10px; border: 1px solid #ddd;"><a href='tel:${phoneNumber}'>${phoneNumber}</a></td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 2, this.innerText)'>${row[1]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;">${formattedDate}</td>
        <td style="padding: 10px; border: 1px solid #ddd;" contenteditable='true' onBlur='updateCell(${index + 2}, 21, this.innerText)'>${row[20]}</td>
        <td style="padding: 10px; border: 1px solid #ddd;"><input type='checkbox' ${isNotLead ? "checked" : ""} onclick='updateCell(${index + 2}, 22, this.checked)'></td>
      </tr>
    `;
  });

  htmlOutput += `</table>
    <button onclick="saveChanges()">Save Changes</button>
    <script>
      function updateCell(row, col, value) {
        fetch('${URL}', {
          method: 'POST',
          body: JSON.stringify({ row, col, value }),
          headers: { 'Content-Type': 'text/plain' }
        }).then(response => response.text()).then(console.log);
      }

      function saveChanges() {
        alert('Changes saved!');
      }
    </script>`;

  return HtmlService.createHtmlOutput(htmlOutput);
}

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
    if (data.row && data.col !== undefined) {
      sheet.getRange(data.row, data.col).setValue(data.value);
      return ContentService.createTextOutput("Cell updated");
    }
  } catch (error) {
    Logger.log("Error: " + error);
    return ContentService.createTextOutput("Data update error");
  }
}

Related

• How do i allow a CORS requests in my google script?

Using fetch with no-cors

Using fetch with no-cors as an alternative solution. The response will be opaque meaning you cannot access the response data. This allows you to check if the request was successfully procces such as no network errors. Also you cannot check the response from backend.

Note: There will be no header and body response since we disabled CORS.

mode: 'no-cors'

Modified Script:

<script>
      const url = "URL HERE";

      function updateCell(row, col, value) {
        // Checking in the log on what data are we passing.
        console.log({ row, col, value });
        const options = {
          method: "POST",
          headers: {
            'Content-Type': 'application/json',
          },
          body: JSON.stringify({ row, col, value }),
          mode: 'no-cors'
        };

        fetch(url, options).then(response => {
          console.log(response);
          // You cannot access response.body or response.headers
          console.log('Request sent successfully');
        })
        .catch(error => {
            console.error('Error:', error);
        });
      }

      function saveChanges() {
        alert('Changes saved!');
      }
</script>

Reference:

• Request: mode property

本文标签： How to save changes from a web app to Google Sheets using Apps ScriptStack Overflow