Automatically Pull Staff Information Into Forms

This page will show you how to automatically pull information such as Team Names or Managers names from the Tearfund Accounts List

This will take around 10 minutes per form you would like to do this for.

Step 1

Open up the form  (in edit mode) that you'd like to pull the data into


Step 2

In the top right hand corner select the 3 dots and then select script editor from the list


Step 3 Group Names only

For manager names skip this step and go straight to step 4

After the last } press the return/enter key and then paste in the whole of the script below, then proceed to step 5

function updateGroupOrTeam() {

 // FormID = Staff Changes Form

 var FormID="1VIiMnCu-CxsBeFE3bxS4FGCvw8YlnDTxLVvLZfwIENg";

 // SheetID = Alt-TAILS

 var SheetID="1_fmS_QD6Bv0_jopvDNXzJ5UgyOx-PfjXBb2JVzmW-14";

 var SheetRange="Teams!H:H";

 var frm=FormApp.openById(FormID);

 var Items=frm.getItems();

  var item1=frm.getItems()[7].asListItem();

 var SheetValues = SpreadsheetApp.openById(SheetID).getRange(SheetRange).getValues()  //All Team Leads

     .slice(1)                                      //Remove header

     .map(function(x) {return x[0];})               //make single column

     .filter(function(x) {return x!=''})            //remove blanks

     .sort();                                       //sort ascending

   SheetValues=remove_duplicates(SheetValues);

   //update the choices for the question

   item1.setChoiceValues(SheetValues);

}


function remove_duplicates(arr) {

   var seen = {};

   var ret_arr = [];

   for (var i = 0; i < arr.length; i++) {

       if (!(arr[i] in seen)) {

           ret_arr.push(arr[i]);

           seen[arr[i]] = true;

       }

   }

   return ret_arr;

} 


Step 4 Manager Names only

Skip this step if you are doing group names

After the last } press the return/enter key and then paste in the whole of the script below, then proceed to step 5

function updateTeamLead() {

 // FormID = Staff Changes Form

 var FormID="1VIiMnCu-CxsBeFE3bxS4FGCvw8YlnDTxLVvLZfwIENg";

 // SheetID = Tearfund-Accounts-List (formerly alt-TAILS)

 var SheetID="1_fmS_QD6Bv0_jopvDNXzJ5UgyOx-PfjXBb2JVzmW-14";

 var SheetRange="Teams!F:F";

 var frm=FormApp.openById(FormID);

 var Items=frm.getItems();

 Logger.log(Items)

 var item1=frm.getItems()[8].asListItem();

 var SheetValues = SpreadsheetApp.openById(SheetID).getRange(SheetRange).getValues()  //All Team Leads

     .slice(1)                                      //Remove header

     .map(function(x) {return x[0];})               //make single column

     .filter(function(x) {return x!=''})            //remove blanks

     .sort();                                       //sort ascending

   SheetValues=remove_duplicates(SheetValues);

   SheetValues.push("Other");

   //update the choices for the question

   item1.setChoiceValues(SheetValues);

}


function remove_duplicates(arr) {

   var seen = {};

   var ret_arr = [];

   for (var i = 0; i < arr.length; i++) {

       if (!(arr[i] in seen)) {

           ret_arr.push(arr[i]);

           seen[arr[i]] = true;

       }

   }

   return ret_arr;

} 


Step 5

Now you will need to retrieve the form ID which you can get by browsing to the form you're working with. The form ID comes from the URL and is the long string of characters highlighted in bold below. You will want to copy this for your form.

https://docs.google.com/forms/d/1VIiMnCu-CxsBeFE3bxS4FGCvw8YlnDTxLVvLZfwIENg/edit

Step 6

Now you'll need to replace the form ID in the script by pasting in the new ID in-between the two "" marks that you see in the image on the left.

This applies for both either manager names or team names


Step 7

Next we'll need to change the script so that it knows which section of the form you would like to update.

Take a look at your form and count from the top  many sections down the section is you'd like to update automatically. 

See example below


On the screenshot to the left the top box you can see is just text so the one titled "Users Name" would 

be the first box. So if we wanted to update this one we would enter the number  0 to the script. The next box would be number 1


(It's just something with the way scripts work is that it counts from 0)

Step 8

Now press the save icon and then click run to ensure that there are no errors and the form should update as desired.


Step 9

Click the stopwatch on the left to open the triggers menu


Step 10

Click the blue add triggers button in the bottom right of the screen

Then ensure that the options look the same as the screenshot. The ones which need to be updated are; 

Select event source

Select type of time based trigger

Select hour interval


Once you've done that press the blue save button.

Finished

That is everything that is required to set up the automatically updating forms.

If you notice in the future that the forms aren't updating then come back to step 8 to see if you get any errors. If not then see if it's updated and proceed to follow Step 9 and Step 10 again.

If you still have issues don't hesitate to get in touch with itsupport@tearfund.org