如何使用Reactjs更新Google Spreadsheets中的特定行? [英] How do I update specific rows in Google Spreadsheets using Reactjs?
本文介绍了如何使用Reactjs更新Google Spreadsheets中的特定行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
需要发生的是，在我的React Web应用程序中有此表格，输入的数据应发送到Google Spreadsheet.
What needs to happen is that from my React web app there's this form and data inputted should be sent to Google Spreadsheet.
- 在电子表格中插入新数据时是否有地方避免使用时间戳记?
- 如何更新特定行? 例如，我有乔，我需要将他的电话号码更新为456
- Is there away to avoid using Timestamp when inserting new data in the spreadsheet?
- How do I update a SPECIFIC row? For example, i have Joe and I need to update his number to 456
| NAME | NUMBER |
|-------|---------|
|Joe |123 |
|Brian |420 |
|Raine |143 |
如何通过网络应用程序执行此操作?
How do I do this coming from the web app?
我已经尝试过了，但是还是没有运气
I've tried this but still no luck
这是来自网络应用程序，在componentDidMount()
This is from the web app with some code in the componentDidMount()
此代码用于表单数据，并防止页面重定向到App脚本链接.基本上，它停留在页面上，甚至重新加载表单.
This code is used to the form data and to prevent the page from redirecting to the App script link. Basically it stays on the page and even reloads the form.
componentDidMount() {
console.log("Contact form submission handler loaded successfully.");
// bind to the submit event of our form
var forms = document.querySelectorAll("form.gform");
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener("submit", handleFormSubmit, false);
}
//document.addEventListener("DOMContentLoaded", loaded, false);
function validateHuman(honeypot) {
if (honeypot) { //if hidden form filled up
console.log("Robot Detected!");
return true;
} else {
console.log("Welcome Human!");
}
}
// get all data in form and return object
function getFormData(form) {
var elements = form.elements;
var honeypot;
var fields = Object.keys(elements).filter(function (k) {
if (elements[k].name === "honeypot") {
honeypot = elements[k].value;
return false;
}
return true;
}).map(function (k) {
if (elements[k].name !== undefined) {
return elements[k].name;
// special case for Edge's html collection
} else if (elements[k].length > 0) {
return elements[k].item(0).name;
}
}).filter(function (item, pos, self) {
return self.indexOf(item) === pos && item;
});
var formData = {};
fields.forEach(function (name) {
var element = elements[name];
// singular form elements just have one value
formData[name] = element.value;
// when our element has multiple items, get their values
if (element.length) {
var data = [];
for (var i = 0; i < element.length; i++) {
var item = element.item(i);
if (item.checked || item.selected) {
data.push(item.value);
}
}
formData[name] = data.join(', ');
}
});
// add form-specific values into the data
formData.formDataNameOrder = JSON.stringify(fields);
formData.formGoogleSheetName = form.dataset.sheet || "responses"; // default sheet name
//formData.formGoogleSend = form.dataset.email || ""; // no email by default
console.log(formData);
return { data: formData, honeypot };
}
function handleFormSubmit(event) { // handles form submit without any jquery
event.preventDefault(); // we are submitting via xhr below
var form = event.target;
var formData = getFormData(form);
var data = formData.data;
// If a honeypot field is filled, assume it was done so by a spam bot.
if (formData.honeypot) {
return false;
}
disableAllButtons(form);
var url = form.action;
var xhr = new XMLHttpRequest();
xhr.open('POST', url);
// xhr.withCredentials = true;
xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhr.onreadystatechange = function () {
console.log(xhr.status, xhr.statusText);
console.log(xhr.responseText);
form.reset();
return;
};
// url encode form data for sending as post data
var encoded = Object.keys(data).map(function (k) {
return encodeURIComponent(k) + "=" + encodeURIComponent(data[k]);
}).join('&');
xhr.send(encoded);
}
function disableAllButtons(form) {
var buttons = form.querySelectorAll("button");
for (var i = 0; i > buttons.length; i++) {
buttons[i].disabled = true;
}
}
}
这是网络应用程序中的代码段表单
This is the snippet form in the web app
<form className="gform" method="POST"
action="GOOGLE APP SCRIPT">
<div className="row Late-body-container">
<div className="col-6">
<select name="Message" className="form-control reason-text" id="selectLateReason" onClick={this.checkOption} onChange={this.loaded}>
<option value="--">--</option>
<option value="Sick Leave">Sick Leave</option>
<option value="Emergency Leave">Emergency Leave</option>
<option value="Family Affairs">Family Affairs</option>
<option value="To travel">To travel</option>
<option value="Other reasons">Other reasons</option>
</select>
</div>
<button type="submit" className="btn btn-send" id="btnSubmit" value="Submit" >Send</button>
</div>
</form>
最后是Google应用程序脚本代码
and then finally this is the Google app script code
/******************************************************************************
* This tutorial is based on the work of Martin Hawksey twitter.com/mhawksey *
* But has been simplified and cleaned up to make it more beginner friendly *
* All credit still goes to Martin and any issues/complaints/questions to me. *
******************************************************************************/
// if you want to store your email server-side (hidden), uncomment the next line
// var TO_ADDRESS = "example@email.net";
// spit out all the keys/values from the form in HTML for email
// uses an array of keys if provided or the object to determine field order
function formatMailBody(obj, order) {
var result = "";
if (!order) {
order = Object.keys(obj);
}
// loop over all keys in the ordered form data
for (var idx in order) {
var key = order[idx];
result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
// for every key, concatenate an `<h4 />`/`<div />` pairing of the key name and its value,
// and append it to the `result` string created at the start.
}
return result; // once the looping is done, `result` will be one long string to put in the email body
}
// sanitize content from the user - trust no one
// ref: https://developers.google.com/apps-script/reference/html/html-output#appendUntrusted(String)
function sanitizeInput(rawInput) {
var placeholder = HtmlService.createHtmlOutput(" ");
placeholder.appendUntrusted(rawInput);
return placeholder.getContent();
}
function doPost(e) {
try {
Logger.log(e); // the Google Script version of console.log see: Class Logger
record_data(e);
// shorter name for form data
var mailData = e.parameters;
// names and order of form elements (if set)
var orderParameter = e.parameters.formDataNameOrder;
var dataOrder;
if (orderParameter) {
dataOrder = JSON.parse(orderParameter);
}
// determine recepient of the email
// if you have your email uncommented above, it uses that `TO_ADDRESS`
// otherwise, it defaults to the email provided by the form's data attribute
var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;
// send email if to address is set
if (sendEmailTo) {
MailApp.sendEmail({
to: String(sendEmailTo),
subject: "Contact form submitted",
// replyTo: String(mailData.email), // This is optional and reliant on your form actually collecting a field named `email`
htmlBody: formatMailBody(mailData, dataOrder)
});
}
return ContentService // return json success results
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) { // if error return this
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": error}))
.setMimeType(ContentService.MimeType.JSON);
}
}
/**
* record_data inserts the data received from the html form submission
* e is the data received from the POST
*/
function record_data(e) {
var lock = LockService.getDocumentLock();
lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
try {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
// select the 'responses' sheet by default
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);
var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var newHeader = oldHeader.slice();
var fieldsFromForm = getDataColumns(e.parameters);
var row = [new Date()]; // first element in the row should always be a timestamp
// loop through the header columns
for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
// mark as stored by removing from form fields
var formIndex = fieldsFromForm.indexOf(field);
if (formIndex > -1) {
fieldsFromForm.splice(formIndex, 1);
}
}
// set any new fields in our form
for (var i = 0; i < fieldsFromForm.length; i++) {
var field = fieldsFromForm[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
newHeader.push(field);
}
// more efficient to set values as [][] array than individually
var nextRow = sheet.getLastRow() + 1; // get next row
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// update header row with any new data
if (newHeader.length > oldHeader.length) {
sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
}
}
catch(error) {
Logger.log(error);
}
finally {
lock.releaseLock();
return;
}
}
function getDataColumns(data) {
return Object.keys(data).filter(function(column) {
return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
});
}
function getFieldFromData(field, data) {
var values = data[field] || '';
var output = values.join ? values.join(', ') : values;
return output;
}
如前所述 我有乔，我需要将他的电话号码更新为456
As mentioned earlier I have Joe and I need to update his number to 456
| NAME | NUMBER |
|-------|---------|
|Joe |123 |
|Brian |420 |
|Raine |143 |
所以预期结果应该是
| NAME | NUMBER |
|-------|---------|
|Joe |456 |
|Brian |420 |
|Raine |143 |
附加说明: 该代码有效，但是当我插入新数据时 我无法摆脱时间戳记.我如何摆脱它?
Additional note: The code works but when I insert new data I can't get rid of the Timestamp. How do I get rid of it?
推荐答案
我将这个答案分为两个部分:
I'm dividing this answer in two parts:
1.摆脱时间戳
正如您在Google Appscript代码中看到的那样:
As you see in your Google Appscript code:
var row = [new Date()]; // first element in the row should always be a timestamp
您只需要删除新的Date()函数，并将其保留为空数组声明即可即可:
You just have to remove the new Date() function at leave it as an empty array declaration:
var row = []; //There is no first element yet.
在此之后，有一个for循环，其索引从1开始以避免时间戳，但是由于我们已将其删除，因此无需从1开始，因此我们必须对其进行更改.
Right after this there is a for loop whose index is starting from 1 to avoid the Timestamp, but since we have removed it there is no need to start at 1, so we have to change it.
for (var i = 0; i < oldHeader.length; i++) { // Now we start at 0 instead of 1
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
....
2.根据条件更新单元格
由于您已经知道用于更新Number的Name的条件(Joe)，因此我们只需要将A中每个单元格的值与字符串'Joe'进行比较:
Since you already know the Name's condition (Joe) to update the Number, we just have to compare the values of each cell in A with the String 'Joe':
function updateCell() {
var doc = SpreadsheetApp.getActiveSpreadsheet(); //this line is not necessary if you just paste this code in yours
var names = doc.getRange("A2:A").getValues(); //We store all the names from A2 to the last one
var newValue = '456';
for (var n = 2; n < names.length + 2; n++){ //We start at 2 since A1 and B1 are the headers of the table
if (names[n - 2] == 'Joe'){ //But the array's first position is 0, so we deduct 2 form n.
doc.getRange("B" + (n)).setValue(newValue);
}
}
}
结果:
这篇关于如何使用Reactjs更新Google Spreadsheets中的特定行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
