无需提交表单即可触发Google脚本 [英] Trigger Google script without Form submission

问题描述

我有一个Google脚本，可以在表单提交的触发器上运行.

I have a Google script that runs on a trigger on form submit.

提交表单后，新行会添加到链接的电子表格中，并且脚本会执行一些操作来创建新文件夹和Google文档.

When the form is submitted a new row is added to a linked spreadsheet, and the script runs some actions to create a new folder and Google doc.

我想通过Zapier(而不是sumbission)添加新行来对此进行修改.

使用Zapier添加新行很容易，

Adding the new Rows with Zapier is easy,

但是，在将脚本触发器从在表单提交中" 设置为在更改中" 事件时，我得到了错误；

However, in setting the script trigger to the 'On change' event from 'On form submit', I get the error;

TypeError:无法读取未定义的属性"0"

我认为在使用 e.values 时，脚本期望表单提交中的这些内容，但是由于Zapier添加的行中不存在这些脚本而导致失败.

I think in using the e.values that the script is expecting these from the form submission, but is failing because they are absent in Zapier added rows.

问题:我不知道如何解决错误并允许脚本在提交表单时与从Zapier添加新行时以相同的方式运行? https://i.stack.imgur.com/vJuRw.png

Problem: I don't know how to resolve the error and allow the script to run in the same way when a form is submitted as when a new row is added from Zapier? https://i.stack.imgur.com/vJuRw.png

我所拥有的；

    function autoFillGoogleDocFromForm(e) {
    var Timestamp = e.values[0];
    var NAME = e.values[1];
  
    var file = DriveApp.getFileById('1234');
    var folder = createNewFolder(); 
    var copy = file.makeCopy(NAME, folder); 
    var newId = copy.getId();
  
    try{
    var doc = DocumentApp.openById(newId).addEditor(email);
    }
    catch(error){
    var doc = DocumentApp.openById(newId);
    }
  
    var body = doc.getBody();  
    body.replaceText('{{Name}}', Name);
    doc.saveAndClose();                                             
                                       
    }

    <!--This creates a Google drive folder -->

    function createNewFolder() {
    var ss = SpreadsheetApp.getActive();
    var names = ss.getSheetByName("Sheet1");
    var POSNAME = names.getRange(names.getLastRow(),
    2).getValue(); 
    var folders = DriveApp.getFoldersByName(NAME);
    while (folders.hasNext()) { 
    folder = folders.next();    
    if(folder.getName()==NAME){
     return folder;
        }
    }
    var parentFolder=DriveApp.getFolderById("1234");
    return parentFolder.createFolder(NAME);
    }

推荐答案

我认为您在值数组中缺少一个级别，需要将 e 转换为 range ，然后再获取值.请记住，电子表格始终具有二维数组.

I think you're missing a level within your value array and you need conver your e into a range before grabbing the values. Remember spreadsheets always have two-dimensional arrays.

我在空白电子表格中设置了以下代码，以便在编辑时运行该代码，并将值放入单元格 a1 和 b1 中.它有过多的变量只是为了说明正在发生的事情.您显然可以减少这些，但最终我认为这会为您提供您要在变量 Timestamp 和 NAME 中查找的值.

I set the below code up in blank spreadsheet to run on edit and it's bringing the values into cells a1 and b1. It's got excessive variables just to illustrate what's happening. You could obviously reduce these but ultimately I think this gets you the values you're looking for your variables Timestamp and NAME.

/**
 * @OnlyCurrentDoc
 */
const mySS = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
function onEdit(e){
  var theRange = e.range;
    var allValues = theRange.getValues();
    var singleRowValues = allValues[0];
    var firstColumnofRowValue = singleRowValues[0];
    var secondColumnofRowValue = singleRowValues[1];
    var Timestamp = firstColumnofRowValue;
    var NAME = secondColumnofRowValue;
    //Test Output
    mySS.getRange("A1").offset(0,0,1,2).setValues([[Timestamp,NAME]]);
}

这篇关于无需提交表单即可触发Google脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！