跨多个电子表格共享相同的Google表格

Question

我已经创建了一个反馈表单，并希望将其从多个电子表格中打开并共享。我在以下帖子Single Google Form for multiple Sheets中使用过修改后的代码。

表单按照预期作为IFRAME打开，但不允许我（或任何用户）填充字段并创建响应。我错过了什么？

function onOpen() { 
 
    var ui = SpreadsheetApp.getUi(); 
 
    // Or DocumentApp or FormApp. 
 
    ui.createMenu('Data Architecture') 
 
     .addItem('Create File Note', 'menuItem1') 
 
     .addSeparator() 
 
     .addSubMenu(ui.createMenu('Feedback & Actions') 
 
      .addItem('Provide Feedback', 'provideFeedback') 
 
      .addItem('Create and Action', 'createAction'))   
 
     .addToUi(); 
 
} 
 

 
function provideFeedback() { 
 
    
 
    var documentName = SpreadsheetApp.getActive().getName(); 
 
    
 
    var dataentityName = SpreadsheetApp.getActiveRange().getCell(1, 1).getDisplayValue(); 
 
    
 
    launchForm(); 
 
    
 
    
 
} 
 

 
function createAction() { 
 
    
 
    var documentName = SpreadsheetApp.getActive().getName() 
 
    
 
    var documentID = SpreadsheetApp.getActive().getId() 
 
    
 
    var dataentityName = SpreadsheetApp.getActiveRange().getCell(1, 1).getDisplayValue() 
 
    
 
    launchForm(); 
 
    
 
} 
 

 
function launchForm(){ 
 

 
    var formID='1pmH3AWiMUczat5uIaZ5zaT--cmDjq9v3W9pePPjwGF0'; 
 
    var form = FormApp.openById(formID); 
 
    var formURL = form.getPublishedUrl(); 
 
    
 
    var response = UrlFetchApp.fetch(formURL); 
 
    var formHtml = response.getContentText(); 
 
    
 
    var htmlApp = HtmlService 
 
    .createHtmlOutput(formHtml) 
 
    .setSandboxMode(HtmlService.SandboxMode.IFRAME) 
 
    .setTitle('Feedback and action form') 
 
    .setWidth(800) 
 
    .setHeight(800); 
 
    
 
    SpreadsheetApp.getActiveSpreadsheet().show(htmlApp); 
 
}

Answer 1

，而不是访问web页面，并生成HTML内容，并再次为它，你可以使用iframe标记与SRC到您的网址为你做它。

function launchForm(){ 

    var formID='1pmH3AWiMUczat5uIaZ5zaT--cmDjq9v3W9pePPjwGF0'; 
    var form = FormApp.openById(formID); 
    var formURL = form.getPublishedUrl()+ "?embedded=true" 

    var htmlApp = HtmlService 
    .createHtmlOutput('<iframe src="'+formURL+'" width="500" height="400" frameborder="0" marginheight="0" marginwidth="0"> </iframe>') 
    .setTitle('Feedback and action form') 
    .setWidth(400) 
    .setHeight(400); 

    SpreadsheetApp.getActiveSpreadsheet().show(htmlApp); 
} 

要有预充式的形式在脚本中使用：
1）获取预充式的形式，通过以下步骤here
2）对于这样一种形式：

预充网址将如下所示：

var formURL = "https://docs.google.com/forms/d/e/{Publish ID}/viewform? 
usp=pp_url& 
entry.1739076204=Jack+Brown& 
entry.1869878870=2017-05-12& 
entry.799903038=Incorrect& 
entry.1996064114=I+am+the+best& 
entry.607023772=Select+Me" 

我分离出了​​各个选项，以便与表单中的条目进行比较。现在要修改条目，您所要做的就是修改每个条目的'='符号后的值。所以要改变你的名字：

var name = "Chuck Norris" 
entry.1739076204='+name+' 

同样，你可以通过修改URL来修改每个输入。下面的代码修改了我托管的特定表单的条目。你将不得不修改，如果按您的需求

function launchForm(){ 

    var publishedURl = "https://docs.google.com/forms/d/e/1FAIpQLSdsHM53jTWJ0Eqn8VSxE5bWHjnD9KXsVBrqLsBwtyJIIsjpnA/viewform" 
    var name = "Chuck Not Norris" 
    var dt = Utilities.formatDate(new Date(), "GMT-05:00", "yyyy-MM-dd") 
    var options1 = ["Correct","Incorrect"] 
    var options2 = "Chuck Norris is google Apps Scripts" 
    var options3 = ["Select Me","Don't select me"] 
    var preEditUrl = publishedURl + '?usp=pp_url&entry.1739076204='+name 
        +'&entry.1869878870='+dt 
        +'&entry.799903038='+options1[0] 
        +'&entry.1996064114='+options2 
        +'&entry.607023772='+options3[0] 

    var htmlApp = HtmlService 
    .createHtmlOutput('<iframe src="'+preEditUrl+'" width="500" height="400" frameborder="0" marginheight="0" marginwidth="0"> </iframe>') 
    .setTitle('Feedback and action form') 
    .setWidth(400) 
    .setHeight(400); 

    SpreadsheetApp.getActiveSpreadsheet().show(htmlApp); 
} 

我们希望，这将让你在正确的方向开始。