如何使用Apps Script获取Google表单标题及其ID?

huangapple go评论67阅读模式
英文:

How to obtain Google Form titles having their IDs using Apps Script?

问题

我有一个脚本可以正确获取通过它们的ID列出的Drive文件列表。

我的数据位于名为P12的表的A列上。这是一个Google Drive文件ID列表。
对于获取Google Drive文件的标题,它确实起作用。然而,在尝试运行脚本时,无法使用Google Forms的ID在视图模式下工作(示例如下),但运行脚本时没有返回任何内容。

https://docs.google.com/forms/d/e/"ID"/viewform

/** 开始 根据文件ID列出文件名 */
function getFileNames() {
const sheet = SpreadsheetApp.getActive().getSheetByName("P12"); // 在这里设置你的表名
const ids = sheet.getRange("A1:a").getValues().flat().filter(String); // 文件ID来源。A2:a 适用于A列,请根据包含文件ID的列进行更改
const table = [];
for (let id of ids) {
try {
var file = DriveApp.getFileById(id);
var name = file.getName();
table.push([name]);
} catch(e) {
table.push(['']);
}} sheet.getRange(1,4,table.length,table[0].length).setValues(table); } // 文件名的目标范围将从第2行第4列(D)开始
/** 结束 根据文件ID列出文件名 */

我尝试将第8行更改为
var file = DriveApp.getFileById(form.getId());

英文:

I have a script that correctly obtains the list of Drive files from their IDs

My data is on Column A of a Sheet named P12. It is a list of Google Drive files IDs.
It does work for obtaining the title of Google Drive files. However, I can't make it work with the IDs of Google Forms in the view mode (example below), but when I run the script it does not return anything.

https://docs.google.com/forms/d/e/"ID"/viewform

/** START List of File Names by FileIDs */
function getFileNames() {
const sheet = SpreadsheetApp.getActive().getSheetByName("P12"); // Setup your Sheetname here
const ids = sheet.getRange("A1:a").getValues().flat().filter(String); // Source of the File IDs. A2:a is for Column A, change to suit the column that has your File IDs
const table = [];
for (let id of ids) {
try {
var file = DriveApp.getFileById(id);
var name = file.getName();
table.push([name]);
} catch(e) {
table.push(['']);
}} sheet.getRange(1,4,table.length,table[0].length).setValues(table); } // Target range for File Names will start in Row 2 of Column 4 (D)
/** END List of File Names by FileIDs */

I tried changing row 8 to
var file = DriveApp.getFileById(form.getId());

答案1

得分: 0

如在我的评论中提到的,您无法使用视图模式 ID 通过应用脚本访问表单。但是,如果您在编辑模式下访问表单时跟踪了 ID,则可以尝试以下方式跟踪这些 ID:

    /** 开始按文件ID列出文件名 */
    function getFileNames() {
     const sheet = SpreadsheetApp.getActive().getSheetByName("P12"); // 在此设置您的表格名称
     const ids = sheet.getRange("A1:a").getValues().flat().filter(String); // 文件ID的来源。A2:a 适用于 A 列,根据您的文件ID所在的列进行更改
     const table = [];
     for (let id of ids) {
      try {
       var file = DriveApp.getFileById(id);
       var name = file.getName();
       var mimeType = file.getMimeType();

       if (mimeType == "application/vnd.google-apps.form") {
        
        var tempForm = FormApp.openById(id);
        var publishedUrl = tempForm.getPublishedUrl();

        table.push([`表单  - ${name} / URL ${publishedUrl}`]);
        
       } else {

         table.push([name]);

       }
      
      } catch (e) {
      
       table.push(['']);
      }
     } sheet.getRange(1, 4, table.length, table[0].length).setValues(table);
    } // 文件名的目标范围将从第2行的第4列(D)开始
    /** 结束按文件ID列出文件名 */
英文:

As mentioned on my comment, you cannot access a form thrugh App script using the View mode ID. However, if you track the IDs when accessing the forms on EDIT mode then you can maybe track the ids in the following way:

/** START List of File Names by FileIDs */
function getFileNames() {
 const sheet = SpreadsheetApp.getActive().getSheetByName("P12"); // Setup your Sheetname here
 const ids = sheet.getRange("A1:a").getValues().flat().filter(String); // Source of the File IDs. A2:a is for Column A, change to suit the column that has your File IDs
 const table = [];
 for (let id of ids) {
  try {
   var file = DriveApp.getFileById(id);
   var name = file.getName();
   var mimeType = file.getMimeType();

   if (mimeType == "application/vnd.google-apps.form") {
    
    var tempForm = FormApp.openById(id);
    var publishedUrl = tempForm.getPublishedUrl();

    table.push([`FORM  - ${name} / URL ${publishedUrl}`]);
    
   } else {

     table.push([name]);

   }
  
  } catch (e) {
  
   table.push(['']);
  }
 } sheet.getRange(1, 4, table.length, table[0].length).setValues(table);
} // Target range for File Names will start in Row 2 of Column 4 (D)
/** END List of File Names by FileIDs */

huangapple
  • 本文由 发表于 2023年4月17日 20:27:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76035158.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定