javascript - 如何使用谷歌脚本向谷歌表格中的指定行和列添加个性化输入?
问题描述
我是 javascript/google 应用程序脚本的初学者,我需要帮助.. google sheet extract
我希望用户能够输入作品的类型(例如 0511)和默认值的数量(例如 2),然后它将自动填充到右行的右单元格中(写入类型的行)和右列(活动列)
这是我已经写过的(它允许我填写活动单元格的前 4 行):
这是在 form.html 中:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form id="myform">
<div class="block form-group">
<label for="date">Date</label>
<input type='date' name='date' id="date" required="required"/>
</div>
<div class="block form-group">
<label for="reference">Référence</label>
<input type='text' name='reference' id="reference" required="required"/>
</div>
<div class="block form-group">
<label for="qtebonne">Quantité bonne</label>
<input type='text' name='qtebonne' id="qtebonne" required="required"/>
</div>
<div class="block">
<table>
<col width="60">
<tr>
<th><strong></strong></th>
<th><strong>M</strong></th>
<th><strong>AM </strong></th>
<th><strong> N</strong></th>
<th><strong>WE</strong></th>
</tr>
<tr>
<td>Equipe</td>
<td><input type="radio" name="equipe" value="M" checked></td>
<td><input type="radio" name="equipe" value="AM"></td>
<td><input type="radio" name="equipe" value="N"></td>
<td><input type="radio" name="equipe" value="WE"></td>
</tr>
</table>
</div>
<div class="block">
<input type="submit" value="Submit" class="action" onclick="form_data()"/>
<input type="button" value="Close" onclick="google.script.host.close()"/>
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
</form>
<script>
document.querySelector("#myform").addEventListener("submit",
function(e)
{
e.preventDefault(); //stop form from submitting
google.script.run.addNewItem(this);
google.script.host.close();//close this dialogbox
}
);
</script>
</body>
</html>
这是在 code.gs 中:
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Remplissage')
.addItem('Ajout Item', 'addItem')
.addToUi();
}
function addItem()
{
var html = HtmlService.createHtmlOutputFromFile('form');
SpreadsheetApp.getUi()
.showModalDialog(html, 'Ajout Nouvel Item');
}
function addNewItem(form_data)
{
var sheet = SpreadsheetApp.getActive();
//sheet.insertColumnAfter(sheet.getLastColumn());
var cellA = sheet.getCurrentCell();
cellA.setValue([form_data.date]);
var cellB = cellA.offset(1, 0); //+1 pour la ligne en dessous
cellB.setValue([form_data.reference]);
var cellC = cellB.offset(1, 0); //+2 pour la ligne encore en dessous
cellC.setValue([form_data.qtebonne]);
var cellD = cellC.offset(1, 0); //+3 pour la ligne encore en dessous
cellD.setValue([form_data.equipe]);
}
这是它在 Excel 上的样子:
解决方案
在 form.html 中:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form id="myform">
<div class="block form-group">
<label for="date">Date</label>
<input type='date' name='date' id="date" required="required"/>
</div>
<div class="block form-group">
<label for="reference">Référence</label>
<input type='text' name='reference' id="reference" required="required"/>
</div>
<div class="block form-group">
<label for="type">Code rebut</label>
<input type='text' name='type' id="type" required="required"/>
</div>
<div class="block form-group">
<label for="qtebonne">Quantité bonne</label>
<input type='number' name='qtebonne' id="qtebonne" required="required"/>
</div>
<div class="block form-group">
<label for="qte_rebut">Quantité rebut</label>
<input type='number' name='qte_rebut' id="qte_rebut" required="required"/>
</div>
<div class="block">
<table>
<col width="60">
<tr>
<th><strong></strong></th>
<th><strong>M</strong></th>
<th><strong>AM </strong></th>
<th><strong>N</strong></th>
<th><strong>WE</strong></th>
</tr>
<tr>
<td>Equipe</td>
<td><input type="radio" name="equipe" value="M" checked></td>
<td><input type="radio" name="equipe" value="AM"></td>
<td><input type="radio" name="equipe" value="N"></td>
<td><input type="radio" name="equipe" value="WE"></td>
</tr>
</table>
</div>
<div class="block">
<input type="submit" value="Submit" class="action" id="Submit"/>
<input type="button" value="Close" onclick="google.script.host.close()"/>
<INPUT type="reset" value="Reset">
</div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
</form>
<script>
document.querySelector("#myform").addEventListener("submit",
function (e) {
e.preventDefault(); //stop form from submitting
google.script.run.addNewItem(this);
// google.script.host.close();//close this dialogbox
});
/* function clearForm() {
document.getElementById('date').value = "";
document.getElementById('reference').value = "";
document.getElementById('type').value = "";
document.getElementById('qtebonne').value = "";
document.getElementById('type').value = "";
document.getElementById('qte_rebut').value = "";
}
document.getElementById("SubmitAndContinueInput").addEventListener("click", myFunctionContinue);
function myFunctionContinue(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(clearForm)
.functionToRunOnFormSubmit(obj);
}
document.querySelector("#myform").addEventListener("SubmitAndContinueInput",
function (e) {
e.preventDefault(); //stop form from submitting
google.script.run.addNewItem(this);
google.script.run
.withSuccessHandler(clearForm)
}); */
</script>
</body>
</html>
在 code.gs 中:
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Remplissage')
.addItem('Ajout Item', 'addItem')
.addToUi();
}
function addItem()
{
var html = HtmlService.createHtmlOutputFromFile('form').setHeight(500);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Ajout Nouvel Item');
}
function addNewItem(form_data)
{
var sheet = SpreadsheetApp.getActiveSheet();
// On détecte la dernière colonne
var lastColumn = detectLastColumn();
Logger.log(form_data);
// On récupère les quatre premières cellules de la dernière colonne
var header = sheet.getRange(1, lastColumn, 4, 1);
// Parce que on form_data.date a la forme yyyy-mm-dd donc :
// split('-') donne une liste ['yyyy', 'mm', 'dd']
// reverse() donne ['dd', 'mm', 'yyyy']
// join('/') donne dd/mm/yyyy comme on veut :)
var formattedDate = form_data.date.split('-').reverse().join('/');
$today = new Date();
$yesterday = new Date($today);
$yesterday.setDate($today.getDate() - 1);
var $dd = $yesterday.getDate();
var $mm = $yesterday.getMonth()+1; //January is 0!
var $yyyy = $yesterday.getFullYear();
if($dd<10){$dd='0'+$dd} if($mm<10){$mm='0'+$mm}
$yesterday = $dd+'/'+$mm+'/'+$yyyy;
// On y ajoute directement les 4 valeurs voulues dans l'ordre
var data = [[formattedDate], [form_data.reference], [form_data.qtebonne], [form_data.equipe]];
header.setValues(data);
Logger.log(form_data.type);
var typeRows = findTypeRanges(form_data.type)
Logger.log(typeRows);
for (var i = 0; i < typeRows.length; i++) {
Logger.log('rowindex: ' + typeRows[i])
sheet.getRange(typeRows[i], lastColumn).setValue(form_data.qte_rebut)
}
}
// Fonction qui va rechercher toutes les lignes correspondant au type passé en argument
function findTypeRanges(type) {
var sheet = SpreadsheetApp.getActiveSheet();
var firstRow = 7 // première ligne à laquelle commence les trucs
var typeCol = 4 // colonne des types
var lastRow = detectLastRow(firstRow, typeCol);
var typesRange = sheet.getRange(firstRow, typeCol, lastRow - firstRow + 1, 1);
// D'après la doc ça permet de chercher dans une range
var textFinder = typesRange.createTextFinder(type);
var allOccurences = textFinder.findAll(); // Chercher toutes occurences
return allOccurences.map(function (range) {
return range.getRow(); // Transforme toutes les ranges en juste la ligne
});
}
// Fonction pour détecter la dernière colonne remplie
function detectLastColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var content = sheet.getRange('F1').getDisplayValue();
var col = 6; // Parce que la colonne qui nous interesse commence à 'F'
while (content !== '') {
col++;
content = sheet.getRange(1, col).getDisplayValue();
}
Logger.log('Last Column: ' + col)
return col;
}
function detectLastRow(firstRow, typeColumn) {
var sheet = SpreadsheetApp.getActiveSheet();
var content = sheet.getRange(firstRow, typeColumn).getDisplayValue();
var row = firstRow;
while (content !== '') {
row++;
content = sheet.getRange(row, typeColumn).getDisplayValue();
}
Logger.log('Last Row: ' + row);
return row;
}
推荐阅读
- javascript - 无法访问已创建工作簿的工作表
- css - 如何在带有 CSS 网格的表单中使用字段集?
- java - 从文件中读取时尝试以另一种方法从数组中获取最大值
- ruby-on-rails - 如何在我的应用程序中处理 has_friendship gem?
- python - flask.cli.NoAppException:无法导入“flaskr.flaskr”
- node.js - 打字稿:未捕获的参考错误:未定义变形
- c# - Xamarin.GooglePlayServices.Ads:如何将捆绑添加到广告请求
- android - 在 google GDPR 同意 sdk 中显示非个性化广告的问题
- node.js - 将本地文件夹中的图像上传到 S3
- javascript - 我无法理解这种奇怪的 JS Flow 语法