Google Workspace and Software Development
Google Apps Script / Javascript
I just had a recent email from a reader who asked how to tidy up a user’s inputted name from say, a Google Form so that all the first letters of each work in the name are capitalised in the same way that the Google Sheets Proper function does.
I thought it would be a good idea to provide a quick reference for the reader and myself for future projects.
Continue reading “Google Apps Script – Javascript: Emulate the “Proper” Google Sheets Function”
Google Apps Script: Google Docs, Document App. Javascript: Spread syntax, Set, IndexOf, Substring
So here is the scenario, imagine you have a big Google Doc. You want to get a list of information from the document that you have noticed are between two sets of characters. Maybe something like this:
This tutorial provides a simple how-to do this. Perhaps the code is exactly what you need for your project. We’ve set it up in a way that is easy to implement in your own project.
Table of Contents
Continue reading “Google Apps Script: Get Google Doc Text Based on Reference Characters”
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, Google Sheets
Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?
The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9. (line 52, file “test”)
Yeap. One of your row lengths is not as long as the width of the column range you set.
This generally occurs when you are manipulating data into a new format before setting it back into your Google Sheet.
Let’s look like some dodgy data I’ve prepared that I want to paste or set backing my spreadsheet.
|
1 2 3 4 5 6 7 8 9 |
var dodgy2dArray = [ [1,2,3,4,5,6], [1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6], [1,2,3], [1,2,3,4,5,6,7,8], [1,2,3,4,5,6,7,8,9], [1] ]; |
As you can see, I’ve got a 2d array for data with 7 rows and with a max row width of 9. This means our data will extend to nine columns at its max.
Now, your first instinct might be that if I take a few chips out of square peg, it should still fit into a square hole. I mean, I’m only subtracting from the square, right?
Then you go off blazing a path through the code, hoody hanging low over your head, punching away at your bespoke mechanical keyboard.
|
1 2 3 4 5 6 |
function stoopid(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1,1,7,9); range.setValues(dodgy2dArray); }; |
With victory within your grasp, you hit run and your world comes crashing down.
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.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi
