将批量数据从Google电子表格填充到Google表单下拉列表中 [英] Populate bulk data from google spreadsheet into google form dropdown list
问题描述
我有一个包含多个部分的google表单,每个部分都有一个下拉列表.我希望从具有匹配名称的电子表格中提取下拉列表的数据.
这是我运行的脚本,但似乎不起作用.
function getDataFromGoogleSheets(){const ss = SpreadsheetApp.getActiveSpreadsheet();const sheet = ss.getSheetByName("DATA");const [header,... data] = sheet.getDataRange().getDisplayValues();const choices = {}header.forEach(function(title,index){choices [title] = data.map(row => row [index]).filter(e => e!==");});返回选择;}函数populateGoogleForms(){const GOOGLE_FORM_ID ="1nsDQ6MtdCci-g5XgLxJ-4XNJ19E9sDz42G6DoFLwiFE";const googleForm = FormApp.openById(GOOGLE_FORM_ID);const items = googleForm.getItems();const choices = getDataFromGoogleSheets();items.forEach(function(item){const itemTitle = item.getTitle();if(itemTitle选择){const itemType = item.getType();开关(itemType){大小写FormApp.ItemType.CHECKBOX:item.asCheckboxItem().setChoiceValues(choices [itemTitle]);休息;大小写FormApp.ItemType.LIST:item.asListItem().setChoiceValues(choices [itemTitle]);休息;大小写FormApp.ItemType.MULTIPLE_CHOICE:item.asMultipleChoiceItem().setChoiceValues(choices [itemTitle]);休息;默认:Logger.log(忽略问题",itemTitle);}}});}
这是数据的副本:
我很惊讶地发现它将根据您的选择进行多种选择.感谢您的代码.
您的最后一个问题涉及这条线,昨晚我在睡觉前就在想这件事,我终于意识到了该附加过滤器的用途.它是针对列的选择最多的列.我最初不明白这一点,因此在构建此行时将其删除:
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;});返回cols;}函数populateGoogleForms(){const GOOGLE_FORM_ID = getGlobal('formid');const googleForm = FormApp.openById(GOOGLE_FORM_ID);const items = googleForm.getItems();const choices = getDataFromGoogleSheets();items.forEach(功能(项目){const itemTitle = item.getTitle();if(itemTitle选择){const itemType = item.getType();开关(itemType){大小写FormApp.ItemType.CHECKBOX:item.asCheckboxItem().setChoiceValues(choices [itemTitle]);休息;大小写FormApp.ItemType.LIST:item.asListItem().setChoiceValues(choices [itemTitle]);休息;大小写FormApp.ItemType.MULTIPLE_CHOICE:item.asMultipleChoiceItem().setChoiceValues(choices [itemTitle]);休息;默认:Logger.log(忽略问题",itemTitle);}}});}
这是我这次使用的数据:
COL1 | COL2 | COL3 |
---|---|---|
10 | 9 | 17 |
18 | 19 | 13 |
14 | 14 | 14 |
3 | 13 | |
4 | 7 | |
6 | ||
1 | ||
8 |
这是表格:
I have a google form with multiple sections, each section with a dropdown list. I wish to pull the data for the dropdown lists from spreadsheet with matching name.
This is the script i run but it doesn't seems to be working.
function getDataFromGoogleSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("DATA");
const [header, ...data] = sheet.getDataRange().getDisplayValues();
const choices = {}
header.forEach(function(title, index) {
choices[title] = data.map(row => row[index]).filter(e => e !== "");
});
return choices;
}
function populateGoogleForms() {
const GOOGLE_FORM_ID = "1nsDQ6MtdCci-g5XgLxJ-4XNJ19E9sDz42G6DoFLwiFE";
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);
}
}
});
}
This is a copy of the data: https://docs.google.com/spreadsheets/d/1jfzuVF64QoMIauyFy5Plxv0nQwukf8sMnFXIAyzyK0s/edit#gid=0
And here is a copy of the google form: https://docs.google.com/forms/d/1nsDQ6MtdCci-g5XgLxJ-4XNJ19E9sDz42G6DoFLwiFE/edit
Please help!
I already had multiple choice form and so I just renamed the headings and generated some data (see table below). I played around with your code because I'd never seen a declaration like in the fourth line. Pretty cool thanks. I tried you code on my form which I created manually and to my surprise it worked the first time.
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);
}
}
});
}
Data Sheet:
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 |
Image of the Populated form:
I was surprised to find that it will take as many choices as you provide. Thanks for the code.
Your last question involves this line and I was thinking about this last night before I went to sleep and I finally realized what that additional filter was for. It's for the columns that don't have as many choices as the one with the most. I originally did not understand that so I removed it when I constructed this line:
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]);col[h]=i+1;idx[h]=i; });
but should have done it this way:
hA.forEach((h, i) => { cols[h] = rows.map(r => r[i]).filter(e=>e!=''); });col[h]=i+1;idx[h]=i; });
And that filter removes all of the blanks at the end of the shorter columns.
So just to be clear here's the final solution:
The code:
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);
}
}
});
}
And here's the data that I used this time:
COL1 | COL2 | COL3 |
---|---|---|
10 | 9 | 17 |
18 | 19 | 13 |
14 | 14 | 14 |
3 | 13 | |
4 | 7 | |
6 | ||
1 | ||
8 |
And here's the form:
这篇关于将批量数据从Google电子表格填充到Google表单下拉列表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!