英文:
Replace pre-set SheetID and SheetName with user input through an interface?
问题
我已部署了一个附加组件（下面是代码），允许我从电子表格中提取行并在Google表单上创建/更新选项。代码本身运行完美。我想学习如何将其升级到下一个级别。我想使SheetID和SheetName可以通过侧边栏或弹出窗口粘贴给不是我自己的用户。我知道这是可能的，但我非常困惑要添加什么。也许将生成用户的Google Drive的窗口合并进去会更容易，然后他们从中选择表格，选择SheetName，然后继续。此外，如果有一种方法使他们只需运行一次此设置，即可在安装在每个表单上的所有表单上运行，那将是很棒的。我知道我提出了很多要求，但即使是指导我查找文档、要查找的术语等方面的指引也会有所帮助。提前感谢！
function openForm(e)
{
populateQuestions();
}
function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
var itemsArray = form.getItems();
itemsArray.forEach(function(item){
googleSheetsQuestions[0].forEach(function(header_value, header_index) {
if(header_value == item.getTitle())
{
var choiceArray = [];
for(j = 1; j < googleSheetsQuestions.length; j++)
{
(googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;
}
item.asCheckboxItem().setChoiceValues(choiceArray);
// 如果使用下拉问题，请使用上面一行代替上面一行。
// item.asListItem().setChoiceValues(choiceArray);
}
});
});
}
function getQuestionValues() {
var ss= SpreadsheetApp.openById('1QeckPxMYSYGMkZ-QggY76u03N1qBKBGL2UEMcUvu7sM');
var questionSheet = ss.getSheetByName('Sheet5');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
英文:
I have deployed an add-on (code below) that allows me to pull rows from a Spreadsheet and create/update options on a Google Form. The code itself works perfectly. I’m looking to learn how I can upgrade it to the next level. I would like to make it so the SheetID and SheetName can be pasted in through a sidebar or pop-up window for users who aren’t me. I know it is possible to do something like this, but I’m very lost on what to add. It might also be easier to incorporate the window that generates the user’s Google Drive, and from there they select the Sheet, select the SheetName, and then go. Additionally, if there’s a way to make it so they only have to run this set-up one time for it to work on every Form it’s installed on, that would be awesome. I know I’m asking for a lot, but even a direction to point me in terms of documentation, words to look up, etc. is helpful. Thank you in advance!
function openForm(e)
{
populateQuestions();
}
function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
var itemsArray = form.getItems();
itemsArray.forEach(function(item){
googleSheetsQuestions[0].forEach(function(header_value, header_index) {
if(header_value == item.getTitle())
{
var choiceArray = [];
for(j = 1; j < googleSheetsQuestions.length; j++)
{
(googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;
}
item.asCheckboxItem().setChoiceValues(choiceArray);
// If using Dropdown Questions use line below instead of line above.
//item.asListItem().setChoiceValues(choiceArray);
}
});
});
}
function getQuestionValues() {
var ss= SpreadsheetApp.openById('1QeckPxMYSYGMkZ-QggY76u03N1qBKBGL2UEMcUvu7sM');
var questionSheet = ss.getSheetByName('Sheet5');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
答案1
得分: 1
我明白了，这只是一个示例，所以非常基础。首先，您需要在应用脚本编辑器内创建一个HTML文件。
使用这个选项：
注意 - 我命名为page。
在<script>标签内，我创建了一个名为handleFormSubmit的函数，它将创建一个由最终用户提交的Sheet ID和Sheet名称组成的对象。
我不知道如何使表单在用户点击提交后关闭，所以我添加了一个额外的关闭按钮。您可以搜索更好的选项。侧边栏菜单如下所示：
回到Code.gs，我创建了一个onOpen菜单：
function onOpen() {
//您可以更改菜单的名称
FormApp.getUi()
.createMenu('formTest')
.addItem('显示菜单', 'showSidebar')
.addToUi();
}
还创建了一个调用侧边栏的函数：
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('page')
.setTitle('Google Sheet数据');
FormApp.getUi()
.showSidebar(html);
}
最后，我修改了populateQuestions函数和getQuestionValues函数，以存储并传递表单中的值到应用脚本。
从populateQuestions函数中，我只修改了这一部分：
function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
到：
function populateQuestions(formObject) {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues(formObject);
还有getQuestionValues函数，从：
function getQuestionValues() {
var ss = SpreadsheetApp.openById('1QeckPxMYSYGMkZ-QggY76u03N1qBKBGL2UEMcUvu7sM');
var questionSheet = ss.getSheetByName('Sheet5');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
到：
function getQuestionValues(formObject) {
// 创建一个文档属性服务来存储文档中的数据
// 这样即使在关闭和打开表单后也可以继续调用它
var documentProperties = PropertiesService.getDocumentProperties();
// 创建文档属性的变量
let sheet_ID = documentProperties.setProperty('sheet ID', formObject.sheetID);
let sheet_name = documentProperties.setProperty('Sheet Name', formObject.sheetName);
// 将新变量传递给应用脚本
var ss = SpreadsheetApp.openById(sheet_ID);
var questionSheet = ss.getSheetByName(sheet_name);
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
参考链接：
英文:
I think I got it, this is just a sample so it’s very basic. First, you need to create an HTML file inside the Apps script editor.
Using this option:
Note - I named mine page.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler().populateQuestions(formObject);
}
</script>
</head>
<body>
<h2>Please add your Google Sheet information</h2>
<form id="myForm" onsubmit="handleFormSubmit(this)">
<label for="sheetID">Add your Sheet ID:</label><br>
<input type="text" id="sheetID" name="sheetID" value="Google Sheet ID"><br>
<label for="sheetName">Add Sheet Name:</label><br>
<input type="text" id="sheetName" name="sheetName" value="e.g. Sheet1"><br><br>
<input type="submit" value="Submit">
<input type="button" value="Close" onclick="google.script.host.close()" />
</form>
</body>
</html>
Inside the <script> tag I created a function called handleFormSubmit which will create an object with the Sheet ID and the Sheet name submitted by the end user.
I didn’t know how to make the form close once the user hit submit, so I added an extra close button. You can search for a better option. The Sidebar menu looks like this:
Back to the Code.gs I created an onOpen menu:
function onOpen() {
//you can change the names of the menu
FormApp.getUi()
.createMenu('formTest')
.addItem('Show menu', 'showSidebar')
.addToUi();
}
=> 
Also created a function to call the sidebar:
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('page')
.setTitle('Google Sheet data');
FormApp.getUi()
.showSidebar(html);
}
Lastly, I modify the function populateQuestions and the getQuestionValues to store and pass the values from the form to the Apps Script.
From the populateQuestions function, I only modify this part from:
function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
To:
function populateQuestions(formObject) {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues(formObject);
And the getQuestionValues function from:
function getQuestionValues() {
var ss= SpreadsheetApp.openById('1QeckPxMYSYGMkZ-QggY76u03N1qBKBGL2UEMcUvu7sM');
var questionSheet = ss.getSheetByName('Sheet5');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
To:
function getQuestionValues(formObject) {
// create a property services to store the data in the document
// so it can continue to call it even after closing an opening the form
var documentProperties = PropertiesService.getDocumentProperties();
// create variables for the document properties
let sheet_ID = documentProperties.setProperty('sheet ID',formObject.sheetID);
let sheet_name = documentProperties.setProperty('Sheet Name',formObject.sheetName);
// pass the new variables to the Apps Script
var ss= SpreadsheetApp.openById(sheet_ID);
var questionSheet = ss.getSheetByName(sheet_name);
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论