dashboard, data informed, data literacy, data studio, education

The best way to allow users to update their data within Looker Studio

In the beginning...

A while ago we had created a system for the teachers and counselors at our school to be able to collect social-emotional learning data. We asked teachers to input information through a Google Form which was connected to a spreadsheet. We then used the spreadsheet to connect to Looker Studio to display the information.

The teachers really enjoyed the user interface of Looker Studio to find information that they needed when they needed it. Of course, because of Looker Studio, we were also able to filter by email and lock things down. This allowed me to make sure that the right people have the right access to the information.

This was great but...

However, as time went on, there was one thing that we noticed that we needed to fix. Teachers were finding that when they found errors or they wanted to make additions to the entries that they put about a student, they would have to go back and refill the entire form again. Then when they started to look at the data come through in Looker Studio, the data would be slightly off because it would have multiple entries with a lot of repeated information. This did not work out very well.

So we need to find a way for the teachers to be able to edit the information that they had already put in or update the information that they had already put in to reflect the current situation.

Here is an example of a dashboard for technology incident reports. This data is collected through a Google Form. When a student has an issue with the items provided by the technology department, an incident report is completed. The results are then displayed in a Google Looker Studio report.

There must be a better way

I started to look for a solution that would allow teachers to input information into Looker Studio. However, this is one of Looker Studio’s limitations. It’s great for viewing data and for exploratory data. It is not a tool for entering and updating data. Since we were getting our data from a Google Form, I started looking for ways to update the form. This is when I came across the idea of being able to create a URL link to edit a form.

Through the use of Google Apps Script, you can do a lot of really neat things. One of those things is to create an edit URL. When installed, a user fills out the form. The app script will generate a pre-filled URL to the form. It will then put the link into a designated location in your spreadsheet. This link can then be added to Looker Studio to allow users to access the pre-filled form and make adjustments.

Build the script

In order to make this work, you will need to be in your spreadsheet. Navigate to Extensions – Apps Script. I have the code already set for you. Just copy and paste it, replacing the code that is currently in the project.

				
					function assignEditUrls() {
 var form = FormApp.openById('yourgoogleformeditkey');

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses Tab Name');

 var data = sheet.getDataRange().getValues();
 var urlCol = 40; 
 var responses = form.getResponses();
 var timestamps = [], urls = [], resultUrls = [];
  for (var i = 0; i < responses.length; i++) {
   timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
   urls.push(responses[i].getEditResponseUrl());
 }
 for (var j = 1; j < data.length; j++) {

   resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
 }
 sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); 
}

There are a few things you will need to update to make it work. The first item you will need to change in the code is the form edit-key. This can be found in the URL when you are editing the form. Take a look at the example below:

“https://docs.google.com/forms/d/THIS_IS_YOUR_KEY/edit”

Next, replace the ‘Form Responses Tab Name’ with the actual tab in your spreadsheet. 99% of the time, this will be Form Responses 1.

Two lines below that, you’ll see the following code: var urlCol = 40;

This is the column number where you would like the URL to be placed when a user submits the form. I would recommend putting this at the end of the spreadsheet.

For example, If the last question in your spreadsheet is column K, you can put the Edit URL into column L. If A = 1, B = 2, then L = 12. You would replace the number 40 in the code with the number 12.

Getting the code to work

Give your code a name and save it. When you click on the “play” button, it will run. You will need to accept permissions to allow the code to run.

Once you have gone through the permissions, run your code again. It is unlikely anything will happen. You’ll need to do one more thing. You need to set up a trigger. In the left panel, find the clock icon. This is for setting up triggers.

Triggers tell the code when to run. Look for the Add Trigger button in the lower right corner. Click the button and a popup will appear.

Set up your menu to look like the image below. The main thing is to make sure the script runs when someone submits a form. On the bottom left, find “Select event type.” Choose “On form submit” and then save. Now, when someone fills out the form, the pre-filled URL will be generated and added to the form responses spreadsheet.

Disclaimer: Any form entries that were submitted before adding this code will not produce the Edit URL. This will only take effect for form entries after the code is installed.

Set up in Looker Studio

Refresh your Looker Studio report, if needed, to bring in the new data. Now when you bring that link directly into Data Studio, it doesn’t look very nice for the user experience.

You need to do some slight modifications to it. One easy way to do this is by creating a calculated field. When you create a calculated field you can change some of the information in the way that it looks.

So in this situation, we are going to create a calculated field for a hyperlink. It is quite straightforward. All we need to do is create a calculated field and then type in the following formula: HYPERLINK(choose the field with the edit URL link, “text you want to show up instead of the link”)

Now when a teacher or a counselor fills out a form about a student and I would like to update the form they simply just need to navigate to the Edit Form field next to that entry. It will open up the form pre-populated with the info information that they had already filled out. The user can go through and make changes and updates as needed.

What do the teachers think?

This is one feature that the teachers have been really, really excited about. It has empowered them to be able to use Data Studio in a way that makes sense. It has also saved time and allowed them to use it effectively as they continue to do their best work and help students become better learners.

There are a ton of Apps Scripts out there that work really well with Google Sheets. Do you know of any others that would help? I would love to hear what else you’re doing with this.

Check out the live Looker Studio Report here.

Chris

Chris Smith is an academic data analyst in the international education field. Throughout his 25 years in education, he noticed schools and educators collecting data but struggling with being overwhelmed or not knowing what to do with the data. So, he began coaching, training, and consulting schools and educators to learn how to discover what data they actually need and how they can turn it into information that leads to insights and informed decision-making.

All Posts