是否可以将js变量发送到Google表格? (不是表格数据) [英] Is it possible to send js variables to google sheets? (Not form data)
问题描述
我有一些根据用户在我的Web应用程序上选择的内容而创建的变量.我想将这些变量发送到Google工作表-这可能吗?我已经将数据从表单发送到Google表格.因为变量是在用户提交表单的同时创建的，所以我想将表单数据以及这些变量放入工作表中.
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))
});
这是我要使用表单数据添加到Google工作表的变量的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变量并将其发送到Google工作表.该表格可以正常工作，那里没有问题.在不使用标签的情况下，如何将变量发送到工作表我什么也找不到.我什至一直在将对象转换为表单数据.而且没有关于如何将表单数据以及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>
Google脚本
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()的body参数，使其既包含表单数据对象又包含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中，请使用
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.
相关问题
参考
这篇关于是否可以将js变量发送到Google表格? (不是表格数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
