AppInventor – Post Data to Google Sheet

Thought I would write this one up on here, can’t take any credit for it, as original idea posted on youtube (credits below), but if I have it here it is easier to grab and use when I need it.

I have previously worked up a solution using a Google Form to submit data to a google sheet from an AppInventor app, but this method uses a standalone google apps script to do the same, but without the baggage of the google form. We will need to:

  • Have a google account (we want to use)
  • Create a spreadsheet, and set this out with the columns we need
  • Create a google apps script
  • Create an App Inventor app with the correct blocks

Continue reading

GAS – Add a custom menu

Adding a simple menu of scripts on your spreadsheet goes something like this:

The first part in ” is what appears on the menu, the second part is the function you are calling. Placing this code in the onOpen function automagically creates the menu each time you start or refresh your google sheet

GAS – Create PDF from Google Form Data, Save to Google Drive and Email It!

I’ll make some assumptions on this one, that you know how to create a Google Form and Responses sheet, and that you can create and edit a Google Doc

Create your Google Form with the questions you want. If you are inside a domain you can automatically collect the user’s email address too

Create a Google Document to use as a template. Leave space for user submissions, and in these spaces include a unique keyword for each answer Continue reading

GAS – format cell background

Easier than it looks!

GAS – format cell number format

In this case a date format:

GAS – format cell wrapping

This script sets the cell wrapping to on for the whole column. Useful for Google Form responses which over write existing cell formats

GAS – Get Array from Data Validated Range

Been happily grabbing data ranges from cell ranges in google sheets for ages, but never spotted that if you have a range where all the cells have data validation in them (e.g. a list of values or list from range), then even if they are blank, e.g. no selection has been made, GAS still counts them if you get the “length” of the array. This little snippet deals with that! Continue reading

GAS – Get the Last Row

Use this function to get the last row in Column A of a spreadsheet

If you want the first empty row, simply add 1 to ct:

GAS – Go to the last row in a sheet

Very often my data can run to hundreds of rows. To quickly go to the last entry use this script:

GAS – Google Sheets – Dynamic Menu and Functions for Sheet Selection

May seem like a sledgehammer to crack a nut, but I was looking for a way to satisfy two things:

  1. Change sheet tabs and know (script-wise) which tab I had come from and was going to
  2. Create a dynamic menu for sheet tab selection, so that I could get the user focused on a single tab, as opposed to having them all there along the bottom

I first tackled this the hard coded long winded way, capturing the names of all sheets and writing an individual function for each, then moved on the a pair forwards / backwards functions, using “getSheets()” to provide the sheet array. This then lead me to going all dynamic!

Started searching, and there were a couple of posts about placing parameters into custom menu items, but nothing that really cracked it, until I came upon this post:

http://clav.cz/google-apps-script-menu-functions-with-parameters/

where Václav Novotný provides a solution to create a dynamic custom menu, and dynamic scripting for functions to go with it. This was setup for a Google Doc, so I need to rework it a bit for my needs. All credit to Václav Novotný though for the meat on the bones. Continue reading

GAS – Google Sheets – Query All Columns with a Script

Came across a problem using query on a table, where I wanted to query all the columns. No easy way to write this into a function if you have 10 + columns, so set about writing a google apps script to handle this. The premise is simple enough, a data table with a list of people’s names in the first column, then a set of columns with colour names assigned to each person:

queryAlldatatable

On the search page a straight forward drop down list of all the colours (using data validation from the datatable range!)

queryAllresult1 Continue reading

GAS – Google Sheets – Query the whole table ? No you can’t !

You would think that this should be straight-forward, but it is not.

A table full of data, with many columns, and everything to the right of Column C will have come from a list of items, but this will be different for each row, and i do not know how many columns there may eventually be. Continue reading

GAS – Insert Checkmark to Cell and Conditional Formatting

A little timesaver. Google Sheets doesn’t have a “Special Characters” selector, so if you want to use one you have to open up a Google Doc, copy the character you want, then back to Sheets to use it. This example shows a simple apps script for inserting a checkmark to a cell. We will then look at conditional formatting of the cell/cells. Continue reading

GAS – Insert new row in Google Sheet using script

Probably not as quick as going Edit > Add Row Above/Below, but provides the starting pointing for extending this little snippet to add content, copy down formulas or “setFormulas” to some of the columns. Useful when working with long lists. This script will add a new row above the existing active cell.

GAS – list files modified in the last 24 hours

If you need to keep track of what has been changed in your Google Drive, this script, set up with a time driven trigger in a google sheet is a lifesaver. Use it nearly every day. (Can’t claim the credit for this one!)

GAS – make a variable from a variable

Clever little ditty this one. Somtimes you need to create a dynamic variable on the fly, perhaps using data pulled in from a sheet or elsewhere that goes into a variable, but you then need to use that value as a variable name and assign it a value. This is how it is done:

I want the value of the variable value01 to become a variable name, result. I want to give result the value green, which is currently in the variable output01. If using a string in output01 you have to have the extra “‘” in the eval(), otherwise a number will not need them.

GAS – Move “Completed” Rows to separate sheet

Very often I create a google form and then need to complete further columns to give feedback or show progress. These can become unwieldy after a short time, so it helps to clear out and archive the finished rows to another sheet. In the example below there is a column that needs to contain “Yes” in order to be moved to the Completed sheet

GAS – random ID

In order to avoid repetition of filenames or clashes in dynamic variables, it is useful to generate a random string to add to the name:

You can then add it to your variable or string like so:

GAS – Restore Data Validation

A downside with GAS spreadsheets is the inability to share them and properly protect them at the same time, whilst remaining usable by an end user. So if I want an “unknown” and “untrusted” user to access the sheet and select from dropdowns etc., i must give them editing rights.

This example shows how to check and restore a cell validation each time the sheet is opened. Continue reading

GAS – Send a Simple Email

Very often you want to email either yourself or another user with output from a script. In very simple terms you need to provide the email address, subject and body

You can, of course, replace the text areas with variables:

GAS – Send an html Email

Much the same as sending a normal email, but this time you include a body with html tags:

GAS – Send Event to Calendar from Spreadsheet

Managing a Google Calendar from a spreadsheet is fairly straight-forward.
You do need the calendar ID and all your ducks lined up on the sheet.

in it’s simplest form:

Continue reading

GAS – Sheets – Clean and Clear Layout

Sometimes, we like our spreadsheet to give focus to what is on it. General formatting: colours, borders etc. do help, but even better is to remove all unneeded columns and rows, along with other distractions. For this to work, the top of the last row needs to be visible. Here is a finished example: Continue reading

GAS – simple data entry

You want to make life easy for your users, and reduce the opportunity for errors when entering data! This simple example allows a cleaning supervisor to enter their inspection scores (items clean / items dirty) for an area, calculates the percentage score, then moves the information to a second data sheet in a list. There is a drop down validation for the locations, and the date field validation allows a double click to bring up a mini calendar. The script is attached to the Submit drawing.

gas-data-entry

At the end of the script, all the fields are cleared ready for the next entry.

GAS – Sort a Single Column

You can do this with a flick of the wrist when working directly on a sheet, but to use a script to do the same thing is slightly more complicated! Google have decided in their infinite wisdom that if you want to sort a range, a column, you will want to sort the data around it as well. This is not always the case. Very often I have a sheet with columns full of unrelated lists: names, locations, dates, etc., and these often need to be sorted alphabetically, by themselves, in order for functions to work further down the line: match / vlookup / filter / index and so on. A few years ago, the clever people on the Google Docs forum came up with a solution, they said it only worked in G Suite for Work or Education, so you mileage may vary when running this in a standalone google sheet? Continue reading

GAS – Three Crackers: 1. Insert image at specific place in a table, 2. Remove extra paragraphs created by 1, 3. Proportionally size your image

The code, used on a google sheet to add an image to a specific location in a google doc table, should be self explanatory! I use this when an image is needed in a spreadsheet data to google doc to pdf creation exercise (hmmm – had better post that up too 😉 Continue reading

GAS – Vertically Align Rows in Sheet

Another simple but useful one, particularly for better presentation of Form Responses, so pop this into your onFormSubmit script:

GAS Folders, Sub Folders and their Files

Google Drive is a wonderful thing, but when it comes to getting at a file with scripts things can get a little quirky. Every file and folder has a unique ID number (28 digits, letters and other marks) which can make it easy to locate it, if you know how. However you can of course have many files, or folders with the same name spread out across your Google Drive, which can create a bit of a problem when trying to find the file or folder. Very often you may have to iterate over “all the folders” in order to find what you want, using names to do this can be problematic.

I find I am routinely needing to grab a list of filenames, file IDs, or file types, and routinely searching the web each time I need to do this, therefore it was about time I tied down the required scripts in one place for reuse. The scripts I have put together will request either a folder name or ID in order to iterate over the files within that folder and return the results to a spreadsheet. The scripts can be used as a base, and edited to suit specific requirements by adding further properties to return, or through the use of if statements to narrow down selected files.

  • You can find the ID of the folder by looking in the url address bar if you have the folder open in Google Drive
  • You can also right click on the folder and then Shareable Link
  • Finding the name should be obvious! – use search in Google Drive to check for folders with the same name. It may be wise to do this occasionally if you use name a lot in your scripts, and to make your folder names unique

In my scripts I have chosen to return the filename, the mime type, the file ID, and to create a download link. I also include a helper script (courtesy of Sandy Good) that presents the mimetype in a more user friendly way. All four folder scripts rely on this. Thanks should also go to Brian P and AD:AM on the google group support forum for their code.

I will add a link to a google sheet which contains all the scripts, just make a copy of it to your own google drive, authorise the scripts and away you go.

Continue reading

GAS Import CSV from GMail Attachment

I have a weekly email sent to me with a csv attachment. This needs to be opened and the contents pasted into a google sheet, the data therein is then used for a variety of other applications running on Google sheets. Fortunately there is a way to automate this and to parse the csv to sheets. I have robbed and amended the code provided by Digital Inspiration to do this. The linked page also includes other examples of scraping csv data from an email attachment. The key to getting this to work is a consistent approach by my email sender, who needs to use the same email address and subject in order that the GMail filter I set up can assign a label to the email. The script is run from the receiving google sheet on a timed trigger, once a week.

The sheet needed sorting to meet requirements of others. Above code kept nice and short thanks to the parsing API provided by Google. Of course, this will work for a single user google account, calling on the gmail of that user. Because I have G Suite it is simple to create a separate account to handle this type of thing.

 

Google Sheets – ArrayFormula with Header/s

The array formula is a wonderful thing in Google Sheets, in most cases providing the opportunity to enter just the one formula and have it automatically fill down as new entries appear from elsewhere.

A clever trick though is to create an array within an array, which allows for header rows to be included.

Continue reading

Google Sheets – Google Apps Script – One to One Meeting Tracker

I was presented with a word document last week with a table full of names of staff and dates of their one to one meetings (who uses spreadsheets for managing this sort of thing ;)), ….”Can you make it so that an email gets sent to the supervisors when it is time for them to hold another one to one meeting with their staff?”

“Yes, of course.”

First job was to grab the data and convert it into a manageable list. Ended up with 8 columns: Name, Supervisor, Job Role, Department, Date of Meeting, Completed, Notes/Comments, date for Next Meeting. The notes were to allow for entries of reasons why the meeting could not take place, e.g. maternity leave or long term sick. There was all sorts of rubbish written in the table that wasn’t a date, people who had left and started during the year, which all had to be cleared out. Eventually got a neat set of data to play with. Continue reading

Google Sheets – replace filter with vlookup for array formula

Cor blimey, this one was a toughy to get to the bottom of! No amount of googling brought me the solution I needed, however by combining resources from three/four places I finally got there.

http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

http://codingisforlosers.com/learn-vlookup-formula-google-sheets/

https://stackoverflow.com/questions/22260953/google-spreadsheet-vlookup-with-arrayformula-plus-multiplication-of-returned-val

https://productforums.google.com/forum/#!searchin/docs/parallel$20lookup$20solution/docs/36A0epDlIdE

Now the filter function in Google Sheets is a lovely thing, but it doesn’t play nicely if you want an array formula, that is a formula that automagically fills down as you add more data to a sheet. So you have to convert over to using vlookup (or other method) to get the result you want. Now on the face of it vlookup will only handle one criteria, but it can be forced, with a fair bit of jiggery pokery to handle multiple criteria, and then work as an array formula.

Here was the problem. Continue reading

Google Sheets – Using Query to Find a Value in any Column

The saga continues on from this and this to find a workable solution to find a value from any column using query. I happened upon another route (see accepted answer), which was to use a spreadsheet function to generate a csv list of columns which could then be applied to the select query. Credits as always to the original poster! I then needed to modify the substitutions to create more of the select query, and found I could build the whole query syntax in two cells then apply these to the query function. I was so chuffed with myself i made up a proper spreadsheet layout with comments and made a video of it for demonstration purposes. Continue reading

Google Sites (Classic) – Embed Live (working) Google Sheet “Range”

This one has been annoying me for a while but I finally arrived at a solution. It is especially usefeul when working inside a G Suite domain, and you wish to share information on a spreadsheet with some selectable cells (e.g. with drop downs) via a classic googlesites page, so for people with the right permissions they can use the functionality of the sheet without causing damage to the underlying data.

The default options and gadgets for presenting a spreadsheet offer too much of the sheet for my liking.

The way to sort this out is to create a web App that can be displayed on the page. The apps script is created on the website (Manage Site > Apps Script), and once the two parts are created inside a project, the app can be deployed and will then show up when added to you page.

So here is what you need to put in your code.gs Continue reading