是否可以将 js 变量发送到谷歌表格?(不是表格数据)

Is it possible to send js variables to google sheets? (Not form data)(是否可以将 js 变量发送到谷歌表格?(不是表格数据))

问题描述

根据用户在我的网络应用程序上选择的内容，我创建了一些变量.我想将这些变量发送到谷歌表 - 这可能吗?我已经将数据从表单发送到谷歌表格.因为变量是在用户提交表单的同时创建的，所以我想将表单数据以及这些变量放入工作表中.

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.

这是当前的js(为了简洁起见，左表html):

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))

                  });

这里是我想用表单数据添加到谷歌表的变量的js:

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);


            });

谢谢

我被卡住的地方是如何获取一个 js 变量并发送到谷歌表格.表格工作在那里没有问题.我找不到任何关于如何在不使用标签的情况下将变量发送到工作表的信息.我什至一直在搞乱将对象转换为表单数据.并且没有关于如何将表单数据以及 js 变量发送到同一张表的说明.

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>

谷歌脚本

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()
  }
}

推荐答案

看来你问题的核心是

如何创建 fetch() 的主体参数，使其包含表单数据对象和 JavaScript 变量中的其他值.

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) 返回一个 FormData 对象.

new FormData(form) returns a FormData object.

要将 HTML 表单中未包含的值添加到现有 FormData，请使用 FormData.append()

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))

通过类似的方式:

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))

要添加更多变量，请根据需要添加更多 fetchBody.append().

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

注意:我还没有测试以上内容.

Note: I didn't tested the above yet.

相关问题

• 将变量附加到FormData

参考文献

• fetch() 简介

这篇关于是否可以将 js 变量发送到谷歌表格?(不是表格数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！