Google Apps Script: Get Google Doc Text Based on Reference Characters

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:

  1. You want to grab all the quoted text in a story and you know that the quoted test is between two sets of quotation marks: “ ”.
  2. You want to grab citations or asides inside different braces, for example, [],{} or ().
  3. You are making a mail merger and you want to grab a specific list of words that the user put in that is to be substituted based on special character identifiers, for example, {{name}}, {{phone}}.
  4. You want to grab all the websites in a Google doc and you know they start will start with https:// and with .gov .

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.

Continue reading “Google Apps Script: Get Google Doc Text Based on Reference Characters”

Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 3                                         <<Intro>>

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.

Continue reading “Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script: Filling out your column data to match the number of columns in a range

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.

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.

With victory within your grasp, you hit run and your world comes crashing down.

column in data does not match number of columns in range Google Apps Script

The Solution

Continue reading “Google Apps Script: Filling out your column data to match the number of columns in a range”

Google Apps Script: Basic Beginners Guide to Using Strings in Code

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:

The resulting log would  look like this:

strings in Google Apps Script

 

Strings, +, \n and arrays[value]

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:

To join two strings together you can concatenate them with a plus sign (+).

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.

To insert a custom variable into a string, one fairly beginner-friendly approach is to put it between two plus signs.

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 – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time

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.

uniqueID DateTimestamp Google Sheets

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.

Continue reading “Google Apps Script – Adding a Unique ID in a Cell on Edit of an Adjacent Cell Using Time”