GAS troubles with cors and fetching data to spreadsheet from html

Question

I need to send data from html form to spreadsheet, but I got mistake after clicking button. I tried to copy the steps from this video https://www.youtube.com/watch?v=yiPnkBEHqf0&list=PLRmEk9smitaVGAAhgU0Pdc2sEs7yxDrEk

in js and html I wrote this:

const url = "https://script.google.com/macros/s/AKfycbzZe824lIxa-hNsO71xoFfq5qXbFaDKhHZeACrQgLMCjU_EjvY/exec";

var loginText = document.getElementById("tLogin");
var tableText = document.getElementById("tTable");
var orderText = document.getElementById("tOrder");
//var kommText = document.getElementById("tKomm");

function testGS(){
    var userInfo = {
            login: loginText.value,
            table: tableText.value,
            order: orderText.value,
            tdate: new Date().toLocaleDateString(),
            //komm: kommText.value,
    };

    fetch(url, {
        method: 'POST',
        //mode: 'no-cors', // no-cors, *cors, same-origin
        body: JSON.stringify(userInfo)
      })
      .then((res) => res.text())
      .then((res) => console.log(res));
      console.log(userInfo);
}           

document.getElementById("del").addEventListener("click", testGS);

<!doctype html>
<html lang="en">
<head>
<title>CLR: PACKING</title>
<meta charset = "UTF-8">
<meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <link rel="stylesheet" href="CSS/main_page_style.css">
    <link rel="icon" href="Image/favicon.png" type="png">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet"
       integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
</head>
<body>
    <div class="conteiner">
        <form novalidate>
                <h6 class="title">PACKING</h6>
                <img src="Image/mainImg.jpg" class="img-fluid" alt="...">
            <div class="dws-input">
                <div class="col-md-3"></div>
                <div>
                    <div>
                        <button id="del" type="button">&lt;======СБРОС</button>
                    </div>
                    <div class="form-floating mb-3 mt-3">
                        <input type="text" class="form-control" novalidate id="tLogin" name= "username" placeholder= "Логин:" autofocus > 
                        <label for="tLogin">Логин:</label>
                    </div>
                    <div class="form-floating mb-3 mt-3">
                        <input type="text" class="form-control" novalidate id="tTable" name= "text" placeholder= "Номер стола:" >
                        <label for="tTable">Номер стола:</label>
                    </div>
                </div>
                <div class="form-floating mb-3 mt-3">
                    <input type="text"  novalidate class="form-control" id="tOrder" name= "text" placeholder= "Заказ:" >
                    <label for="type3">Заказ:</label>
                </div> 
            </div>  
        </form>
    </div>
    <script src="JS/fetchNew.js"></script>

</body>



</html>

in apps script I wrote this:

function doGet() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("LOG_history");  
  const data = ws.getRange("A1").getDataRegion().getValues();
  const headers = data.shift();

  const jsonArray = data.map(r => {
    let obj = {};
     headers.forEach((h , i) => {
       obj[h] = r[i];
     });
    return obj;
  })
  const response = [{status: 200, data: jsonArray}];
  return sendJSON_(response);
}

function doPost(e){

  let jsonResponse;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("LOG_history");
  const headers = ws.getRange(1,1,1,ws.getLastColumn()).getValues()[0];
  const headersOriginalOrder = headers.slice();
  headersOriginalOrder.shift();
  //remove id columns header
  headers.shift();
  headers.sort();

  const body = e.postData.contents;
  const bodyJSON = JSON.parse(body);
  const headersPassed = Object.keys(bodyJSON).sort();

  if(!compareTwoArray_(headers, headersPassed)){
      jsonResponse = [{status:500, message:"Invalid Arguments Passed"}];
      return sendJSON_(jsonResponse);
  }

  const arrayOfData = headerOriginalOrder.map(h => bodyJSON[h]);
  
  const aoaIds = ws.getRange(2,1,ws.getLastRow()-1,1).getValues();
  const newIDNumber = getMaxFromArrayOfArray_(aoaIds) + 1;
  arrayOfData.unshift(newIDNumber);
  ws.appendRow(arrayOfData);
  
  return ContentService.createTextOutput("ok");
}

//return true if all ites are the same
function compareTwoArray_(arr1, arr2){
  if (arr1.length !== arr2.length) return false;
    for (let i = 0; i < arr1.length; i ++){
      if (arr1[i] !== arr2[i]) return false;
    }
  return true;
}

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

//return the highest number / id
function getMaxFromArrayOfArray_(aoa){
  let maxID = 0;
  aoa.forEach(r => {
    if (r[0] > maxID) maxID = r[0];
  });
  return maxID;
}

I aloweed all the permissions I know but I have this mistakes on picture with core and fetch. Help!!!

Have you tried removing this line in the apps script `.setMimeType(ContentService.MimeType.JSON);`? — iansedano, Aug 09 '21 at 11:14
@iansedano I tried to delete .setMimeType(ContentService.MimeType.JSON); but result is the same. I have the same mistake — Dmitriy Rudakov, Aug 10 '21 at 13:16

iansedano · Accepted Answer · 2021-08-11T12:51:38.967

0

(Important edit at end)

Make sure you are only sending plain text information

Modify your function sendJSON_ to

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

That or set it to .setMimeType(ContentService.MimeType.PLAIN_TEXT)

You can also try setting the header from the client-side JS to "Content-Type": "text/plain"

fetch(url, {
        method: 'POST',
        headers: {"Content-Type": "text/plain"},
        body: JSON.stringify(userInfo)
      })
      .then((res) => res.text())
      .then((res) => console.log(res));

This should then work without issues.

If you are still not getting success, and if you don't need a response from the web app, but just need to send info. You can activate no-cors. The request will get sent, but any response it gives will not be available to the client-side.

fetch(url, {
    method: 'POST',
    mode: 'no-cors',
    body: JSON.stringify(userInfo)
})
.then((res) => res.text())
.then((res) => console.log(res));

Minimal Example:

Start a new project.

Paste this code into Apps Script:

function doPost(e) {
  return ContentService.createTextOutput("ok"); 
}

Navigate to Deploy > New Deployment
Select type 'Web app'
Execute as: "Me".
Who has Access: "Anyone".
"Deploy"

Take a note of the exec URL.

On a local machine, in a local server or in the browser console execute:

url = "[YOUR_DEPLOYMENT_URL]"

fetch(url, {
        method: 'POST',
        headers: {"Content-Type": "text/plain"},
        body: JSON.stringify({"test":1})
      })
.then(r => r.text())
.then(t => console.log(t))

When I followed these steps, I get on the console:

ok

Reference

Edit

Even after making these changes it seemed that the CORS error persisted on the project.

After some debugging I found that simply by changing the property name in userInfo from tdate to anything else, made it work.

I don't know why tdate as a property in a strigified JSON would cause issues, but it seems to be a reserved word of sorts.

That said, I have tried to reproduce this issue in a new project and can't seem to do so!

edited Aug 11 '21 at 12:51

answered Aug 09 '21 at 11:23

iansedano

6,169
2
12
24

thank you, I will try your help near time and answer your question. Thank you!) – Dmitriy Rudakov Aug 09 '21 at 12:37
I think I need answer from server to check result of action)) – Dmitriy Rudakov Aug 10 '21 at 13:10
I have the same result. Mistake in picture and nothing goes to table – Dmitriy Rudakov Aug 10 '21 at 13:19
May be I need to take cors like here? https://cloud.google.com/storage/docs/configuring-cors#rest-apis – Dmitriy Rudakov Aug 10 '21 at 13:27
Did you remember to redeploy the project after making the changes? You can't configure CORS on Apps Script web apps unfortunately. – iansedano Aug 10 '21 at 13:52
I redeployed my project and but the same thing((( – Dmitriy Rudakov Aug 10 '21 at 14:57
Can you tell me, may be answer on my question is here? https://stackoverflow.com/questions/43871637/no-access-control-allow-origin-header-is-present-on-the-requested-resource-whe – Dmitriy Rudakov Aug 11 '21 at 04:26
Have you deployed your web app with the "who can access" as "Anyone" ? – iansedano Aug 11 '21 at 08:58
I have added a small working example, can you try? The solutions in the link you posted will unfortunately not work with Apps Script :( – iansedano Aug 11 '21 at 09:09
I have the same trouble with executing code. I made new index.html page with button and executed your fetch. All things I did, like you told, but in the console I get the same mistakes like in picture.... Is it problem on the server may be? – Dmitriy Rudakov Aug 11 '21 at 10:05
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235878/discussion-between-iansedano-and-dmitriy-rudakov). – iansedano Aug 11 '21 at 10:07

GAS troubles with cors and fetching data to spreadsheet from html

1 Answers1

Make sure you are only sending plain text information

Minimal Example:

Reference

Edit