我已经有了多项选择表格,所以我只是重命名了标题并生成了一些数据(见下表)。我玩弄了你的代码,因为我从未见过像第四行那样的声明。非常酷,谢谢。我在我手动创建的表单上尝试了您的代码,令我惊讶的是它第一次就成功了。
function getDataFromGoogleSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Sheet1");
const [hA, ...rows] = sheet.getDataRange().getDisplayValues();
const cols = {};//just made some minor changes to fit my personal likes in labeling
const col={};
const idx={};
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]);col[h]=i+1;idx[h]=i; });
return cols;
}
function populateGoogleForms() {
const GOOGLE_FORM_ID = getGlobal('formid');//Have the id stored in a spreaddsheet. Other than that though it's exactly the same code
const googleForm = FormApp.openById(GOOGLE_FORM_ID);
const items = googleForm.getItems();
const choices = getDataFromGoogleSheets();
items.forEach(function (item) {
const itemTitle = item.getTitle();
if (itemTitle in choices) {
const itemType = item.getType();
switch (itemType) {
case FormApp.ItemType.CHECKBOX:
item.asCheckboxItem().setChoiceValues(choices[itemTitle]);
break;
case FormApp.ItemType.LIST:
item.asListItem().setChoiceValues(choices[itemTitle]);
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item.asMultipleChoiceItem().setChoiceValues(choices[itemTitle]);
break;
default:
Logger.log("Ignore question", itemTitle);
}
}
});
}
数据表:
COL1 |
COL2 |
COL3 |
COL4 |
COL5 |
COL6 |
COL7 |
COL8 |
COL9 |
COL10 |
0 |
0 |
1 |
2 |
0 |
1 |
2 |
1 |
0 |
0 |
2 |
2 |
2 |
1 |
1 |
2 |
2 |
2 |
2 |
2 |
0 |
1 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
1 |
填充表格的图像:
我惊讶地发现它会接受你提供的尽可能多的选择。感谢您的代码。
你的最后一个问题涉及到这一行,昨晚我睡觉前一直在思考这个问题,我终于意识到这个额外的过滤器是做什么用的。它适用于那些没有那么多选择的列。我最初不明白这一点,所以我在构建这一行时删除了它:
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]);col[h]=i+1;idx[h]=i; });
但应该这样做:
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]).filter(e=>e!=''); });col[h]=i+1;idx[h]=i; });
该过滤器会删除较短列末尾的所有空白。
所以要明确的是,这是最终的解决方案:
代码:
function getDataFromGoogleSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Sheet1");
const [hA, ...rows] = sheet.getDataRange().getDisplayValues();
const cols = {};
const col={};
const idx={};
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]).filter(e=>e);col[h]=i+1;idx[h]=i; });
return cols;
}
function populateGoogleForms() {
const GOOGLE_FORM_ID = getGlobal('formid');
const googleForm = FormApp.openById(GOOGLE_FORM_ID);
const items = googleForm.getItems();
const choices = getDataFromGoogleSheets();
items.forEach(function (item) {
const itemTitle = item.getTitle();
if (itemTitle in choices) {
const itemType = item.getType();
switch (itemType) {
case FormApp.ItemType.CHECKBOX:
item.asCheckboxItem().setChoiceValues(choices[itemTitle]);
break;
case FormApp.ItemType.LIST:
item.asListItem().setChoiceValues(choices[itemTitle]);
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item.asMultipleChoiceItem().setChoiceValues(choices[itemTitle]);
break;
default:
Logger.log("Ignore question", itemTitle);
}
}
});
}
这是我这次使用的数据:
COL1 |
COL2 |
COL3 |
10 |
9 |
17 |
18 |
19 |
13 |
14 |
14 |
14 |
3 |
13 |
|
4 |
7 |
|
|
6 |
|
|
1 |
|
|
8 |
|
这是表格: