send data in object from html by fetch to spreadsheet

Question

I try to send data from html inputs to spreasheet like author in 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");

function testGS(){
    var userInfo = {
            login: loginText.value,
            table: tableText.value,
            order: orderText.value,
            sdate: new Date().toLocaleDateString(),
    };
    fetch(url, {
        method: 'POST',
        headers: {"Content-Type": 'application/json'},
        body: JSON.stringify(userInfo)
        })
        .then((res) => res.text())
        .then((text) => console.log(text));
}           

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/[email protected]/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= "Login:" 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= "Table:" >
                        <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= "Order:" >
                    <label for="type3">Заказ:</label>
                </div> 
            </div>  
        </form>
    </div>
    <script src="JS/fetchNew.js"></script>

</body>

</html>

In GAS 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 = headersOriginalOrder.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 need to send the data from inputs html page into object UserInfo in js-function and appendthem to spreadsheet but nothing happens.

If I put userInfo object in fetch like this "body: JSON.stringify(userInfo)" I get cor-mistake. How can I organize that? thank you!

I have one notice: if I write in fetch "mode: 'no-cors'," then the data appends to spreadsheet, but return-info from doPost doesn't show. I need return info....((

aNewb · Accepted Answer · 2021-08-11 14:45:45Z

1

Do you have to stringify your object to pass between server and client? I have this code in my html for getting data back from the server.

function succeed (res)   {
        alert('Begin succeed' );
        console.log('Begin succeed' );
        if (res == null)    {
          alert('Error: response was null!' );
          console.log('Error: response was null!' );
          return;
        }
        
        try  {
          alert(JSON.stringify(res) );
          console.log(JSON.stringify(res) );
          alert(res);
          console.log(res);
        }    catch (err)   {
          alert('catch error trying to display res' );
          console.log('catch error trying to display res' );
        }
        
        try  {
          document.getElementById('listDisplay').innerHTML = JSON.stringify(res);
        }    catch (err)   {
          alert('catch error trying to put res into textarea' );
          console.log('catch error trying to put res into textarea' );
        }
      }
      
      function fail (err) {
        alert('Begin fail' );
        console.log('Begin fail' );
        alert('Error occurred', err);
        console.log('Error occurred', err);
        
        document.getElementById('listDisplay').innerHTML = 'ERROR: ' + err;
      }

answered Aug 11, 2021 at 14:45

aNewb

3082 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitriy Rudakov Over a year ago

thank you. I think your script will come in handy, when I will show the results of actions. But I now I need to append data in row of spreadsheet))

Collectives™ on Stack Overflow

send data in object from html by fetch to spreadsheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related