refresh google form item list onOpen

2019-01-27 10:53发布

问题:

I have a google form with an item list for all the employees. I am populating this list from a MySQL table. I am trying to refresh the form with an updated employee list every time the google form is opened. I have an on open event trigger but for some reason the list does not get updated. Please let me know.

  function onOpen(){
    var form = FormApp.openById('239jelkwpeowppd-3843ikeifd89wered');

    var conn = mySQL();
    var itemList = getEmpList(conn);

    var item = form.getTitle('Approver:');
    item.setChoiceValues(itemList);
  }

  function getEmpList(conn){
    var sql = conn.createStatement();
    var ds = sql.executeQuery('SELECT empNAME FROM emp');
    var columns = ds.getMetaData().getColumnCount();
    var list=[];
    while (ds.next()) {
      var rowString = '';
      for (var col = 0; col < columns; col++) {
        rowString += ds.getString(col + 1) + '\t';
        list.push(rowString);
      }
    }
    return list;
  }

  function mySQL(){
    var dbConn = Jdbc.getConnection("jdbc:mysql://dbURL","user","password");
    return dbConn;
  }

回答1:

Short answer

The on open event occurs when the form is opened in edit mode, not when the form is opened in view (response submission) mode, and only if the user has permission to edit the form.

References

On the following quotes emphasis is mine:

From https://developers.google.com/apps-script/guides/triggers/

onOpen(e) runs when a user opens a spreadsheet, document, or form that he or she has permission to edit.

From https://developers.google.com/apps-script/guides/triggers/installable

Installable triggers always run under the account of the person who created them. For example, if you create an installable open trigger, it will run when your colleague opens the document (if your colleague has edit access), but it will run as your account.