javascript - 如何将多个 html 表单发送到同一文件/数据库中的不同谷歌工作表？我有一张谷歌表，总共有 3 张表
问题描述
我有一个谷歌表，它有 3 个表，我总共有3 个 html表单。Html 不要从谷歌应用脚本运行。
这是我必须从 html 发送到 google sheet 的代码。但它只能发送到一张纸，我会发送到另一张纸。这是一个用于谷歌应用脚本的 javascript，它是一个 .cs 文件。代码来自这里https://medium.com/@dmccoy/how-to-submit-an-html-form-to-google-sheets-without-google-forms-b833952cc175
// original gist: https://gist.github.com/willpatera/ee41ae374d3c9839c2d6
function doGet(e){
return handleResponse(e);
}
// Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
我不明白这部分。我尝试添加更多变量和 PropertiesService。我无法得到结果。
// Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
解决方案
这是您问题的解决方案。https://www.youtube.com/watch?v=V4JMjmwHPEg
我在 youtube 上做了一个教程，你可以查看 youtube 描述中的链接以获取源代码。
您必须为电子表格中的每张工作表创建重复的功能。检查您需要在电子表格 .gs 文件中添加的以下代码。有关更多信息，您可以查看提供的 youtube 链接。
// See front-end example at: http://codepen.io/notarazi/pen/yMqyXX
// Usage
// 1. Enter sheet name where data is to be written below
//var SHEET_NAME;
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
//Sheet1
try {
var action = e.parameter.action;
if (action == 'create') {
return create(e);
}
else if (action == 'retrieve') {
return retrieve(e);
}
else if (action == 'update') {
return update(e);
}
else if (action == 'delete') {
return del(e);
}
else if (action == 'findRowId'){
return findRowId(e.parameter.findstr);
}
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
//Sheet2
try {
var action = e.parameter.action;
if (action == 'create_1') {
return create_1(e);
}
else if (action == 'retrieve_1') {
return retrieve_1(e);
}
else if (action == 'update_1') {
return update_1(e);
}
else if (action == 'delete_1') {
return del_1(e);
}
else if (action == 'findRowId'){
return findRowId(e.parameter.findstr);
}
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function getDataArr(headers, e){
var row = [];
// loop through the header columns
for (i in headers){
var d= new Date();
if (headers[i] == "tid"){ // special case if you include a unix Timestamp column
row.push(d.getTime());
}else if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
return row;
}
function create0(e){
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": 99}))
.setMimeType(ContentService.MimeType.JSON);
}
function findRowId(data) {
var SHEET_NAME = "Published Online";
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
//var column = sheet.getRange(column + ":" + column); // like A:A
var column = sheet.getRange(1,1,sheet.getLastRow(),1);
var values = column.getValues();
var data1=Number(data);
var row = 0;
var result = 0;
while ( values[row] && values[row][0] !== data ) {
row++;
}
if (values[row][0] === data)
result= row+1;
else
result = -1;
//result += data1;
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "values": result}))
.setMimeType(ContentService.MimeType.JSON);
}
function findInColumn(data) {
var SHEET_NAME = "Sheet1";
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
//var column = sheet.getRange(column + ":" + column); // like A:A
var column = sheet.getRange(1,1,sheet.getLastRow(),1);
var values = column.getValues();
//data=1490461629269;
var testdata = Number(data);
var row = 0;
while ( values[row] && values[row][0] !== testdata ) {
row++;
}
if (values[row][0] === testdata)
return row+1;
else
return -1;
// return ContentService
// .createTextOutput(JSON.stringify({"result":"success", "values": row}))
// .setMimeType(ContentService.MimeType.JSON);
}
function findInColumn_s2(data) {
var SHEET_NAME = "Sheet2";
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
//var column = sheet.getRange(column + ":" + column); // like A:A
var column = sheet.getRange(1,1,sheet.getLastRow(),1);
var values = column.getValues();
//data=1490461629269;
var testdata = Number(data);
var row = 0;
while ( values[row] && values[row][0] !== testdata ) {
row++;
}
if (values[row][0] === testdata)
return row+1;
else
return -1;
// return ContentService
// .createTextOutput(JSON.stringify({"result":"success", "values": row}))
// .setMimeType(ContentService.MimeType.JSON);
}
function findInRow(data) {
var SHEET_NAME = "Published Online";
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rows = sheet.getDataRange.getValues();
for (var r=0; r<rows.length; r++) {
if ( rows[r].join("#").indexOf(data) !== -1 ) {
return r+1;
}
}
return -1;
}
/***********************************************************************************************************************************************************************************/
//SHEET 1 CREATE
function create(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet1';
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var numColumns = sheet.getLastColumn();
var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = getDataArr(headers, e);
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 1 RETRIEVE
function retrieve(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet1';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numRows = sheet.getLastRow();
var numColumns = sheet.getLastColumn();
var range = sheet.getRange(1, 1, numRows, numColumns);
var values = range.getValues();
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "values": values}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 1 UPDATE
function update(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet1';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numColumns = sheet.getLastColumn();
var rowId = findInColumn(e.parameter.tid);
var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var row = getDataArr(headers, e);
//var rowId = e.parameter.rowId;
var tid= row[0];
// more efficient to set values as [][] array than individually
sheet.getRange(rowId, 1, 1, numColumns).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "tid": tid}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 1 DELETE
function del(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet1';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var rowId = findInColumn(e.parameter.tid);
sheet.deleteRow(rowId);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "rowId": rowId}))
.setMimeType(ContentService.MimeType.JSON);
}
/***********************************************************************************************************************************************************************************/
/***********************************************************************************************************************************************************************************/
//SHEET 2 CREATE
function create_1(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet2';
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var numColumns = sheet.getLastColumn();
var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = getDataArr(headers, e);
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 2 RETRIEVE
function retrieve_1(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet2';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numRows = sheet.getLastRow();
var numColumns = sheet.getLastColumn();
var range = sheet.getRange(1, 1, numRows, numColumns);
var values = range.getValues();
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "values": values}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 2 UPDATE
function update_1(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet2';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numColumns = sheet.getLastColumn();
var rowId = findInColumn_s2(e.parameter.tid);
var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var row = getDataArr(headers, e);
//var rowId = e.parameter.rowId;
var tid= row[0];
// more efficient to set values as [][] array than individually
sheet.getRange(rowId, 1, 1, numColumns).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "tid": tid}))
.setMimeType(ContentService.MimeType.JSON);
}
//SHEET 2 DELETE
function del_1(e) {
var SHEET_NAME = e.parameter.sheet_name || 'Sheet2';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var rowId = findInColumn_s2(e.parameter.tid);
sheet.deleteRow(rowId);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "rowId": rowId}))
.setMimeType(ContentService.MimeType.JSON);
}
/***********************************************************************************************************************************************************************************/
//SETUP
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}推荐阅读
- spring - 如何在 Spring Boot 中创建具有自定义授权类型的 WebClient
- javascript - Express-validator：规范化的电子邮件字段是否不需要清理？
- machine-learning - 使用混淆矩阵进行聚类准确性检查
- python - 如何在用户输入中输入 NULL 值以进行索引切片？
- python - asyncio 如何理解非阻塞操作的任务已完成
- javascript - 允许依赖 uuid() 的节点模块在客户端和服务端执行
- reactjs - 函数内部的开关在 Typescript 中无法正常工作
- java - 有没有办法在主方法中创建方法局部内部类的对象？
- python - 在 Elasticsearch 中比较两个日期
- c# - 如何在 Asp.Net MVC 中以“hh:mm tt”格式从 DateTime 获取时间