The geeky things that keep me happy.
Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays
In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.
This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.
Google Apps Script, Javascript, strings
Hey there, Yagi here, you’ve probably stumbled across this page from a link from one of my other in-depth tutorials. This is just a quick primer on Javascript Strings in Google Apps Script for the non-coder.
Here’s a bare-bones example of how a string of text might come together in your code:
|
1 2 3 4 5 6 7 8 9 |
function string(){ var meatz = ["chicken", "liver"]; var message = "\nI like "+ meatz[0] +".\n"+ "I like "+ meatz[1] +". \n"+ "Meow Cat, Meow Cat "+ "They deliver." Logger.log(message); }; |
The resulting log would look like this:
If you are unfamiliar with basic string syntax and joining (concatenation), basically you can write text inside a single (‘) or double(“) quotation marks. For example:
|
1 2 3 4 |
"This is a string" 'This is also a string' |
To join two strings together you can concatenate them with a plus sign (+).
|
1 2 3 4 5 |
"I like chicken."+ "I like liver. "+ "Meow Cat, Meow Cat "+ "They deliver." |
Just remember to put a space inside your quotation mars at the end if you are going to join another word or the words will not be spaced and theywillbealltogether.
This is handy when you want to put things on separate lines in your code but it won’t be on a separate line when it is displayed. You can use the newline escape sequence \n to return the next part of a string to a new line.
|
1 2 3 4 5 6 7 8 9 |
"I like chicken.\n"+ "I like liver. \n"+ "Meow Cat, Meow Cat "+ "They deliver." Displays: I like chicken. I like liver. Meow Cat, Meow Cat. They deliver. |
To insert a custom variable into a string, one fairly beginner-friendly approach is to put it between two plus signs.
|
1 2 3 4 5 6 7 8 9 10 11 |
var meatz = ["chicken", "liver"]; "I like "+ meatz[0] +".\n"+ "I like "+ meatz[1] +". \n"+ "Meow Cat, Meow Cat "+ "They deliver." Displays: I like chicken. I like liver. Meow Cat, Meow Cat. They deliver. |
You can see in the meatz variable above, we have put our two meat items in an array. An array always starts at zero (0). So to get chicken we would do, meatz[0].
Meow get on back to your main tutorial and get learn’n.
~Yagi
Google Apps Script, onEdit, Date, Google Sheets
Here is the scenario. You have a small business and you want to store your customers, products and sales information on separate Google sheets.
You’re probably going to have someone from your staff enter new customers, products are sales transactions.
We know if we get them to enter unique ID’s in manually that mistakes are going to get made. So how about we automate this process with unique ID’s based on date-timestamps.
Why use a date-timestamp to create a unique ID?
Every year is unique. Every day, hour, minute, second and millisecond of that year creates a unique number. This mean that a new unique id will be create every millisecond for us.
Wow! Wow! Wow! Yagi! Just hold it one damn minute! You could have multiple results each millisecond that would each be the same number!
Well, true if we were running a loop generating and publishnig our date-timestamp, we could have multiple numbers. However, we are generating this unique ID as an onEdit function when the user adds some information in Google Sheets to a cell and an adjacent cell returns our unique number.
The user’s input and then the calls to and from the server to the Google Sheet will be sufficiently slow enough not to have a number generated multiple times a millisecond, so we are safe there.
We have the main idea of what we are planning, let’s move onto the example.
Google Sheets, Forms, WordPress, HTML5, a touch of Javascript
Note: This is part 2 of a larger project. Each part of the project is self-contained if you wish to reference just one particular aspect. Alternatively, you can follow along with the project to practise workflow and learn about Google Forms, Sheets, WordPress integration and a little HTML5 and Javascript. You can access the beginning of the project here:
In our previous post, I showed you how to create a Google Form and embed it into a WordPress post. The end result looked like this.
Feel free and complete the survey for fun and the unadulterated joy of surveys!
In this tutorial, we will add a live Google pie chart of our results that updates every 30 seconds so that our viewer, …erh…you, can see the results as they come in.
Here is what our Chart will look like.
Javascript, WordPress, Your Affiliate Program’s Campaign Link
When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so pleased to see a growing community in my comments sections.
Your support has really helped me to produce more and, I hope, produce better content. Thanks.
Just take me to the code!!!
