Is it possible to send js variables to google sheets? (Not form data)

Question

I have some variables that get created depending on what a user selects on my web app. I want to send those variables to a google sheet - is this possible? I already send data from a form to google sheets. Because the variables get created at the same time a user submits the form I want to put the form data as well as those variables into the sheet.

Here is the current js (left form html out to be concise):

$('#CompleteOrder').on('click', function(event){
                        event.preventDefault();

                        const scriptURL = 'https://script.google.com/macros/s/...................'
                        const form = document.forms['submit']

                        fetch(scriptURL, { method: 'POST', body: new FormData(form)})
                        .then(response => console.log('Success!', response))
                        .catch(error => console.error('Error!', error.message))

                  });

And here is the js for the variables I want to add to the google sheet with the form data:

var ti = 1;
            $.each(products, function (i) {
                var productID = Number(products[i]['id']);
                var productImage = products[i]['image'];
                var productColor = products[i]['color'];
                var productSize = products[i]['size'];
                var productName = products[i]['name'];
                var productPrice = Number(products[i]['price']);
                var productQty = Number(products[i]['qty']);

                body = body + '<tr><td><span>' + productName + '</span></td><td><span>' + productColor + '</span></td><td><span>' + productSize + 'L</span></td><td><span data-prefix>$</span><span >' + productPrice + '</span></td><td><span>' + productQty + '</span></td><td><span data-prefix>$</span><span>' + (productPrice * productQty) + '</span></td></tr>';
                total = total + (productPrice * productQty);


            });

Thanks

Edit:

Where I am stuck is how to take a js variable and send to a google sheet. The form works there is no problem there. I can't find anything on how to send a variable to a sheet without using tags. I've even been messing with converting the object to form data. And no where is there an explanation on how to send form data as well as js variables to the same sheet.

Form

<form name="submit">
                            <div class="form-group">
                                <label for="cus-name">Name</label>
                                <input type="text" name="name" class="form-control form-control" id="cus-name"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-address">Street Address</label>
                                <input type="text" name="address" class="form-control form-control" id="cus-address"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-city">City</label>
                                <input type="text" name="city" class="form-control form-control" id="cus-city"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-state">Province</label>
                                <input type="text" name="province" class="form-control form-control" id="cus-state"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-zipcode">Zipcode</label>
                                <input type="text" name="zip" class="form-control form-control" id="cus-zipcode"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-phone">Phone Number</label>
                                <input type="text" name="number" class="form-control form-control" id="cus-phone"></input>
                            </div>
                            <div class="form-group">
                                <label for="cus-email">Email Address</label>
                                <input type="email" name="email" class="form-control form-control" id="cus-email"></input>
                            </div>




                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
                        <button type="submit" class="btn btn-success" id="CompleteOrder">Complete Order</button>
                    </div>

                                           </form>

Google Script

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

By the way, regarding being concise, see minimal reproducible example. — Rubén

Rubén Rubén · Accepted Answer · 2018-08-09T16:01:52

It looks that the core of your question is

How to to create the fetch()'s body argument in such way that it includes both the form data object and other values from JavaScript variables.

new FormData(form) returns a FormData object.

To add a value not included on the HTML form to an existing FormData use FormData.append()

One way to apply the above to the code to add one value is to replace

fetch(scriptURL, { method: 'POST', body: new FormData(form)})
                        .then(response => console.log('Success!', response))
                        .catch(error => console.error('Error!', error.message))

by something like:

var something = 'New value';
var fetchBody = new FormData(form);
fetchBody.append('newKey',something);
fetch(scriptURL, { method: 'POST', body: fetchBody})
                        .then(response => console.log('Success!', response))
                        .catch(error => console.error('Error!', error.message))

To add more variables, add more fetchBody.append() as needed.

Note: I didn't tested the above yet.

Related Q

Append var to FormData

References

Introduction to fetch()

Is it possible to send js variables to google sheets? (Not form data)

2 Answers