Author: bazroberts

Apps Script Basics (16) – Form page navigation

In this post, we’re going to expand on my previous post on setting up form validation and improve the clocking in and out form we set up, so that the same form can be used for different employees. We’re going to use this example to see how page navigation can work, both to move to certain pages and to display the Submit button on a page. There will be 4 pages to our form and on the first page, we’ll have a drop-down menu where the employee will choose their name and then click Next. This will then take them to their particular page in the form. Fred will go to page 2, Wilma to page 3, and Betty to page 4. (I’m not sure what happened to Barney!)   On their personal pages, the employees will have the same options. They will enter their employee number and they will state whether they are clocking in or out. Then they will Submit the form. Some of the code below is similar to the post on Form Validation. You can read more details there. The code Line 1: Set up the function. Line 3: Create the new form. Line 4: Add a title to the form using setTitle(). Page 1 On the first page, we’re going to have a drop-down menu where the employee will select their name before clicking...

Read More

Apps Script Basics (15) – Form validation

In this post, we’re going to look at automatically setting up validation on a form. Validation allows us to control what the user inputs on the form, for example, to make sure they enter a number, make sure they write more than 10 words, etc. CLOCKING IN & OUT FORM In the example below, we’re going to set up a simple clocking in and out form, which will require the employee to enter a 4 digit code. It will then add validation to the form to check that the code is valid. We’ll also make the questions required to make the user have to fill them out before submitting the form. The code Either in a Google Sheet or Google Form, write the following code. We’re going to create a new form, create the employee number question, then add validation to it. Line 1: Set up the function. Line 3: Create a new form using FormApp.create() and give it a name in the brackets. Line 4: To give the form a title, get the form and set the title using setTitle(). Now, let’s add the employee number question. Line 7: The question will require the employee to type in a number, so we need to add a text item. So, we get the form and use addTextItem(). Don’t add a semi-colon on the end, as we going to add...

Read More

Apps Script Basics (14) – Using Form Responses

In my previous posts we looked at how we can create Google Forms from Google Sheets. This time we’ll look at how we can work with the responses a form user submits. We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form. To show some practical uses of this, we’ll do the following: Set up a simple problem-reporting log, which will email the relevant parties the problem in a classroom. We’ll do this via the Sheet and via the Form. Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added. Set up a simple appointment system, which will update itself as people take the appointments, leaving only the available ones on the Form. Problem-reporting log & email (Sheet version) Here, we have a Google Form which a teacher can fill in if they have a problem in their classroom. They fill in the problem and click on the classroom, then submit the form. The Form is linked to a Google Sheet and the form response is stored on the sheet called ‘Form Responses 1″. This is where we’ll get the response and then email it to the IT department, so they can come and sort the problem out. The code The code...

Read More

Apps Script Basics (13) – Adding different types of questions to a Form

In this post, we’re going to look at how we can add different types of questions to a Google Form from a Google Sheet. As an example, we’re going to create a questionnaire with 9 different question types. In our sheet we have the questions and various options we’re going to use. Column B has the question types, which will tell our script which type of question to set up. Column C has the questions. Columns D to K, contain the various options for each question. Overview of script Our script will: Create a form Get the data from the sheet Loop down each row checking the type of question Run the appropriate function to make the corresponding question and options Creating a new Google Form Line 2: Create a new form using FormApp. I’ve put it outside the main function to create a global variable so that the form is visible throughout the script and in the different functions. Here it’s stored in the variable form. Getting the data from the Sheet Line 4: Set up the main function, makeQuestionnaire. Line 6: Get the sheet called “Sheet1”, where the questions are stored. Line 7: Get all the data on the sheet, using getDataRange() and getValues(), then store it in the variable, data. Note, with this we’re only making one call to the Sheet as we’ll then be working...

Read More

APPS SCRIPT BASICS (12) – Creating & updating a multiple question form

Following on from my previous post on creating and updating Google Forms, here we’ll look at adding and updating multiple questions to a form. Creating multiple questions in a Google Form Here in a Google Sheet, I have a set of reading comprehension questions which I want to add to a Google Form. There are 5 questions and each question has 4 multiple-choice options. For simplicity, I’ve just put options a to d, but you could add different possible answers here, including full sentences. From the Sheet, open the Script Editor. Here’s the full piece of code we’re going to use. It’s going to get the data from Sheet1, then store the questions and options in variables. Then it’s going to create a new form and add those questions and options to it. Let’s look at it line by line. Line 1: Set up a function called “createFormFromData()”. Line 2: Get the sheet called “Sheet1” and store it in the variable sh. Line 5: Get all the data on the sheet using the getDataRange() method and store it in data. Here’s what the contents of data looks like. Line 6: We need to know how options there are. Getting the length of the array data, we will see there are 7 rows (note, the 7th row are the answers not pictured in the Sheet screenshot above), but we are...

Read More

Archives

eBooks available

Pin It on Pinterest