Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. Add a Table Control to your form/workflow. In this example Contact Information form, our table has four columns: lineNum (Text), Contact Type (Dropdown), Phone (Phone), Email (Email).
  2. Set up your Google Sheet with column names that match the control names in your form. You'll need two additional elements:
    1. The first column in your submissions Google Sheet must be the line number. Prepopulate this with line numbers. In our example this tab is named "ContactInfo".
    2. Add another tab in your sheet named "getline". You can name the columns whatever you want. The first column will be used as a read query, and the second column (cell B2) will represent the last available row in your sheet. Set it to "1" for now. Your business rule will update it.
  3. Add two business rules. Replace the info shown in <> in these examples with your Google Sheet details (e.g. replace <spreadsheet key> with your spreadsheet's key, found in the spreadsheet URL.)  
    1. Get Line Numbers:

      Code Block
      /*member nextln, password, user, results */
      var event = form.load;
      var event2 = infoTableRepeat.itemAdded; //name of your table or repeat control
      
      var x;
      var id;
      var idm;
      
      if (form.load){
        var headers = {"user":"<google username>","password":"<OAuth Token>" };
      
        for (var i = 0; i < lineNum.value.length; i++){ //lineNum is a control in your table/repeat
      	//query to read from the extra tab in your sheet that you named 'getline'. The query is A1="A2".
          var readquery = '/google/spreadsheets/query/key/<spreadsheet key>?wsname=getline&query=<cell A1>="<cell A2>"';
      	//query to update that same tab.
          var updatequery =
              '/google/spreadsheets/update/key/<spreadsheet key>?wsname=getline&query=<cell A1>="<cell A2>"';
      
          eval('x=' + http.get(readquery, headers));
          //sets the line numbers in the table
          id = x.results[0].nextln; //"nextln" is the ColB header (cell B1 in your tab)
          if (lineNum[i].value.length === 0){
            lineNum[i].value = id; 
            id = id * 1 + 1;
          }
      
          if (id !== 'unknown') {
            eval('x=' + http.put(updatequery + '&updates=nextln=' + id, null, headers, false)); //update nextln with the next available line number (# of rows in your table + 1)
          }
        }
      }
    2. Update Rows from Table

      Code Block
      /*member password, user, ContactType, Email, Phone, firstname, lastname, linenumber*/
      
      if (trigger.clicked){
      
      //Variables for controls that are NOT in the repeat
        var fname = firstName.value;
        var lname = lastName.value;
        
        //Login
        var headers = {"user":"<google username>","password":"<OAuth Token>" };
      
         for (var i = 0; i < ContactType.value.length; i++){ //ContactType is a column in your table. This loop repeats for each table row that has a value in this column.
          var ln = lineNum[i].value; //Variable that gets the line number set by your Get Line Number rule, corresponds to an available row in the Google Sheet
      	//query to update the tab "ContactInfo" when the first column "line" equals the line number (ln) in your table
          var updatequery = '/google/spreadsheets/update/key/<your spreadsheet key>?wsname=ContactInfo&query='+encodeURIComponent('line="'+ ln + '"');
          //variables for controls that ARE in the repeat
          var a = ContactType[i].value;
          var b = Phone[i].value;
          var c = Email[i].value;
          //identify which controls to send to the Google Sheet. Syntax is "GS column header":value. In this case our values are held in local variables.
          var params ={"firstname":fname,"lastname":lname,"ContactType":a,"Phone":b,"Email":c};
          var updateparams = '&updatesjson='+encodeURIComponent(JSON.stringify(params));
      	//http.put updates the Google sheet
          eval('x=' + http.put(updatequery + updateparams, null, headers, false, false));
        }
      }
  4. Replace the info shown in <> in these examples with your Google Sheet details (e.g. replace <spreadsheet key> with your spreadsheet's key, found in the spreadsheet URL.) 
  5. In use, this form will
    1. Populate hidden lineNum table column with the last available line number from the Google Sheet, and increment that number for each row. It will then write back to the Google Sheet to update the last available line number (on the "getline" tab) so that if another form is opened before this one is submitted, they won't overwrite one another's submission. The image below shows the lineNum column for demo.
    2. When the trigger is clicked, update the Google Sheet rows that match the table's lineNum rows with the parameters you have set, which can include both form data outside the table and within the table for that specific row.