CODEWITHSUNDEEP
javascriptgoogle-sheets-apifetch-api
Thirsty
Thirsty

Reputation: 317

Google sheets API gives Uncaught (in promise) TypeError: NetworkError when attempting to fetch resource

I am trying to connect a very basic web, only HTML/CSS/JS, with google sheets. When I submit the form to upload the data to the sheet I get:

Uncaught (in promise) TypeError: NetworkError when attempting to fetch resource.

​I have been reading all over that this is because of CORS but I cannot figure out how to solve it. The code that I have is very simple, in the google script:

function doPost(e){
  let jsonResponse;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('web')
  const headers = ws.getRange(1, 1, 1, ws.getLastColumn()).getValues()[0];

  const body = e.postData.contents;
  const bodyJSON = JSON.parse(body);

  jsonResponse = ws.appendRow([bodyJSON.name, bodyJSON.email])    
  return sendJSON_(jsonResponse) 
}

function sendJSON_(jsonResponse) {
  return  ContentService.createTextOutput(JSON.stringify(jsonResponse)).setMimeType(ContentService.MimeType.JSON);
}

I have reduced the code of my HTML to the minimum and its:

<!DOCTYPE HTML>
<html lang="en">
<head>
    <meta charset="UTF-8">
</head>
<body>
    <div id='form' class="form">
    <form id="customerForm">
        <input type="text" id="name" placeholder="name">
        <input type="email" id="email" placeholder="Email">
        <button type="submit" id="submitButton">Send</button>
    </form>

    <script>
        function afterSubmit(e){
        url = 'https://script.google.com/macros/s/........../exec'
        var name = document.getElementById('name')
        var email = document.getElementById('email')
        var info = {
            name: name.value,
            email: email.value
        };
        console.log(info);

        fetch(url, {
            method: 'POST',
            cache: 'no-cache',
            redirect: 'follow',
            body: JSON.stringify(info)
        })
        .then(res => res.json())
        .then(res => {
            console.log(res)
        })
          }

        document.getElementById('customerForm').addEventListener('submit', afterSubmit);

    </script>

</body>

</html>

I have also checked that when I publish the google sheets script as web app I have the options correctly set to: execute the app as: me ([email protected]) who has access to the app: Anyone, even anonymous

The way I get the error in console is simply opening the HTML file with the browser, fill data and press send.

Upvotes: 2

Views: 1212

Answers (1)

iansedano
iansedano

Reputation: 6481

Modified Apps Script

function doPost(e){
  let jsonResponse;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('web')
  const headers = ws.getRange(1, 1, 1, ws.getLastColumn()).getValues()[0];

  const body = e.postData.contents;
  const bodyJSON = JSON.parse(body);

  jsonResponse = {"name": bodyJSON.name, "email": bodyJSON.email} // MODIFIED
  ws.appendRow([jsonResponse.name, jsonResponse.email]) // MODIFIED
}

  • I believe your main problem was with this line

    jsonResponse = ws.appendRow([bodyJSON.name, bodyJSON.email])

    If you see the appendRow method it says the return type is a Sheet. So in theory, without my modification, the next line should have returned an error. What do the executions in your Apps Script dashboard say? That said, it might have still been able to update the sheet.

    With this modified, your HTML successfully updated the script even though it threw a bunch of errors in the browser console.

  • You don't need to explicitly return anything either for it to function, which is why I omitted it here.

Modified HTML

function afterSubmit(e) {
  url =
    "https://script.google.com/macros/s/<SCRIPT_ID>/exec";
  var name = document.getElementById("name");
  var email = document.getElementById("email");
  var info = {
    name: name.value,
    email: email.value,
  };
  console.log(info);

  fetch(url, {
    method: "POST",
    cache: "no-cache",
    mode: "no-cors", // to prevent CORS errors
    redirect: "follow",
    body: JSON.stringify(info),
  })
    .then((res) => console.log(res))
    .catch((err) => console.log(err));
  e.preventDefault(); // to prevent form from reloading page
}

document.getElementById("customerForm").addEventListener("submit", afterSubmit);
  • Only thing added here was the mode: "no-cors".
  • The e.preventDefault() at the end supressed a "Failed to Fetch" error I was getting because the form submit automatically reloaded the page. It seems that since the page reload happened before a response could come back it would just say "failed" although it was successful in updating the script.
  • The returned object is actually blank because of the no-cors argument, see response type.

If you wanted a more meaningful response, @Tanaike has in previous posts suggested something using async, like this:

const fetchData = async (url, info) => {
  var json;
  try {
      const response = await fetch(url, {
        method: 'POST',
        body: JSON.stringify(info),
      });
      if (response != "") json = await response.json();
      console.log('Success:', JSON.stringify(json));
  } catch (e) {
      console.log('Errors:', e.message)
  }
  return json;
}

async function main() {
    const res = await fetchData(url, info);
    console.log(res);
}

main()

Which would also require adding this to the Apps Script side:

return ContentService.createTextOutput(JSON.stringify(e)).setMimeType(ContentService.MimeType.JSON);

References

Upvotes: 1

Related Questions