将HTML表格（用户输入）传输到Google表格 [英] Transfer a HTML table (user input) to google sheets
问题描述
我想使用Google的应用脚本将一些用户输入数据从html页面传输到我的google工作表。我可以为单个值执行此操作，但无法找到为表执行此操作的好方法。该表将由用户动态创建（固定列数和动态行数）。
i want to transfer some user input data from a html page to my google sheet with google´s app scripts. I can do that for single values but could not figure out a nice way to do that for a table. The table will be dynamically created by the user (fix number of columns and dynamic number of rows).
下面的代码是显示单个值示例的代码，缺少整个表的解决方案。
Bellow is the code which shows the example for a single value and is missing a solution for the entire table.
一个想法是遍历表并创建一个包含表数据的数组，并将其解析到后端脚本中，然后将其写入工作表，但也许有更明智的方法。
One idea was to iterate over the table and create a array of arrays with the table data and parse that to the backend script to then write it to the sheet but maybe there is a smarter way to do so.
HTML前端：
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<!-- Get the user name -->
<label>Name:</label><input type="text" id="username">
<!-- Get the table data -->
<table class="table table-bordered" id="tbl_posts">
<thead>
<tr>
<th>#</th>
<th>column 1</th>
<th>column 2</th>
<th>column 3</th>
<th>column 4</th>
</tr>
</thead>
<tbody id="tbl_posts_body">
<tr id="rec-1">
<td><span class="sn">1</span>.</td>
<td>11</td>
<td>12</td>
<td>13</td>
<td>14</td>
</tr>
</tbody>
<tbody id="tbl_posts_body">
<tr id="rec-2">
<td><span class="sn">1</span>.</td>
<td>21</td>
<td>22</td>
<td>23</td>
<td>24</td>
</tr>
</tbody>
</table>
<button id="sendbutton"> Send </button>
<script>
document.getElementById("sendbutton").addEventListener("click",parse_to_backend);
function parse_to_backend(){
// transfer the username
var name = document.getElementById("username").value;
google.script.run.TransferUsername(name);
// transfer the table data
// HERE I NEED SOME CODE
};
</script>
</body>
</html>
JS后端：
function doGet() {
return HtmlService.createHtmlOutputFromFile("FrontEndScript");
}
function TransferUsername(name){
var URL = "https://docs.google.com/spreadsheets/...";
var Spreadsheet = SpreadsheetApp.openByUrl(URL);
var Worksheet = Spreadsheet.getSheetByName("Data");
Worksheet.appendRow([name,]);
}
function TransferTabledata(tabeldata){
var URL = "https://docs.google.com/spreadsheets/...";
var Spreadsheet = SpreadsheetApp.openByUrl(URL);
var Worksheet = Spreadsheet.getSheetByName("Data");
// Here i need some code
}
推荐答案
您可以使用Sheets API直接解析和粘贴HTML。
You can use Sheets API to parse and paste HTML directly.
let table = document.getElementById('tbl_posts').outerHTML;
google.script.run.pasteHtml(table);
Code.gs代码段：
function pasteHtml(table) {
var ss = SpreadsheetApp.getActive();
var req = {
requests: [
{
pasteData: {
html: true,
data: table,
coordinate: {
sheetId: ss.getSheets()[1].getSheetId(), //second sheet!A1
rowIndex: 0,
columnIndex: 0,
},
},
},
],
};
Sheets.Spreadsheets.batchUpdate(req, ss.getId());
}
要阅读：
- pasteData请求
- = a href = https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services rel = nofollow noreferrer>启用表格高级服务
To Read:
- pasteData Request
- Enabling Sheets Advanced Services
这篇关于将HTML表格（用户输入）传输到Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
