更新 Google 表单选择信息

发布于 2025-01-14 03:35:51 字数 764 浏览 2 评论 0原文

我们想要制作一个 Google 表单，其中有从工作表 (Sheet1) F 列中拉出的下拉选项，从第 3 行开始向下。但是，F 列中有公式，具体为： =IF(D$="","", CONCATENATE(C$," - ",D$))，因此某些单元格显示为空白，而其他单元格则具有可见文本。

我们尝试使用下面的代码不起作用。关于如何通过从 F 列中提取选择来完成这项工作，但当然忽略空白单元格，有什么帮助吗？

var form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

const current = sheet.getRange(3,6,sheet.getLastRow()-1,6).getValues()
var range = sheet.getDataRange();
 
 var rangeList = current.map(function (row, i) {
 
 for (var i=rangeList; i<range.length; i++) { 
  if (row[5] == "") return;
  var matched = row[5];

  const Question = form.getItemById ("620176576")
  Question.asListItem().setChoiceValues(matched)
 }
 })
}

原文

We want to make a Google Form where there are dropdown options pulled from column F of the sheet (Sheet1), beginning in row 3 on down. However, column F has formulas in it, specifically: =IF(D$="","", CONCATENATE(C$," - ",D$)), so that some of the cells appear blank while others have visible text.

The code we attempted to use below does not work. Any help on how to make this work by pulling choices from column F, but of course ignoring blank cells?

var form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

const current = sheet.getRange(3,6,sheet.getLastRow()-1,6).getValues()
var range = sheet.getDataRange();
 
 var rangeList = current.map(function (row, i) {
 
 for (var i=rangeList; i<range.length; i++) { 
  if (row[5] == "") return;
  var matched = row[5];

  const Question = form.getItemById ("620176576")
  Question.asListItem().setChoiceValues(matched)
 }
 })
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心头的小情儿 2025-01-21 03:35:51

您必须使用 filter 来仅获取不为空的值。

尝试下面的示例脚本：-

const form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

const current = sheet.getRange(3,6,sheet.getLastRow()-3).getValues().flat().filter(r=> r)  //filtering out blank values

const Question = form.getItemById("620176576")

Question.asListItem().setChoiceValues(current)

参考：

过滤器（）

You've to use filter to only get the values which are not null.

Try below sample script:-

const form = FormApp.openById('1Hg4TvEZUnzIMZI_andbwHQ3jtaIBLOZsrTkgjSwVcAY')

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

const current = sheet.getRange(3,6,sheet.getLastRow()-3).getValues().flat().filter(r=> r)  //filtering out blank values

const Question = form.getItemById("620176576")

Question.asListItem().setChoiceValues(current)

Reference:

filter()

回复收藏 0 原文

~没有更多了~