gpt4 book ai didi

javascript - 如何使用 Reactjs 更新 Google 电子表格中的特定行?

转载 作者:行者123 更新时间:2023-11-30 13:57:25 27 4
gpt4 key购买 nike

需要做的是,从我的 React 网络应用程序中有这个表单,输入的数据应该发送到 Google Spreadsheet。

  1. 在电子表格中插入新数据时,是否可以避免使用时间戳?
  2. 如何更新特定行?例如,我有乔,我需要将他的号码更新为 456
| NAME  |  NUMBER | 
|-------|---------|
|Joe |123 |
|Brian |420 |
|Raine |143 |

我如何从网络应用程序执行此操作?

我试过了,还是不行

https://mashe.hawksey.info/2018/02/google-apps-script-patterns-conditionally-updating-rows-of-google-sheet-data-by-reading-and-writing-data-once/

这是来自网络应用程序,在 componentDidMount()

中有一些代码

此代码用于表单数据并防止页面重定向到 App 脚本链接。基本上它停留在页面上,甚至重新加载表单。

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


}

这是网络应用中的代码片段表单


<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 应用程序脚本代码


/******************************************************************************
* 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

| NAME  |  NUMBER | 
|-------|---------|
|Joe |123 |
|Brian |420 |
|Raine |143 |

所以预期的结果应该是

| NAME  |  NUMBER | 
|-------|---------|
|Joe |456 |
|Brian |420 |
|Raine |143 |

补充说明:代码有效,但是当我插入新数据时我无法摆脱时间戳。我该如何摆脱它? enter image description here

最佳答案

我将这个答案分为两部分:

<强>1。去掉时间戳

如您在 Google Appscript 代码中所见:

var row = [new Date()]; // first element in the row should always be a timestamp

您只需删除新的 Date() 函数,将其保留为空数组声明:

var row = []; //There is no first element yet.

紧接着有一个for循环,其索引从1开始以避免时间戳,但由于我们已经删除了它,所以不需要从1开始,所以我们必须改变它。

 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。根据条件更新单元格

因为您已经知道 Name 的条件 (Joe) 来更新 Number,我们只需将 A 中每个单元格的值与字符串 'Joe' 进行比较:

enter image description here

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

结果:

enter image description here

关于javascript - 如何使用 Reactjs 更新 Google 电子表格中的特定行?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56983207/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com