[et_pb_section bb_built=”1″ admin_label=”section”][et_pb_row admin_label=”row” background_position=”top_left” background_repeat=”repeat” background_size=”initial”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” border_style=”solid” background_position=”top_left” background_repeat=”repeat” background_size=”initial” _builder_version=”3.0.51″ custom_margin=”-100px|||”] When working with spreadsheets we can spend quite a long time making them look good and if we have to make the same sheets over and over again, that’s a lot of wasted time. Here we’ll look at… Continue reading Pimp up your Sheet – Programmatically!
Author: bazroberts
Create a Book inventory with Google Sheets & Apps Script
Here we’re going to make a simple book inventory, where we’ll be able to control the location of the books and also find out where a book is. This uses a mixture of GAS code and Sheets functions. We’ve got 3 sheets: Front page – This is what the user will see and use. In… Continue reading Create a Book inventory with Google Sheets & Apps Script
Automatically emailing info from a form submission
Here we’ll look at how to set up automatic emails, which contain information submitted by the Google Form user. The beauty of this is that the information is sent to you (and others) without you having to do anything and without you having to check the spreadsheet to see if there has been a submission.… Continue reading Automatically emailing info from a form submission
Request form – Sending automatic emails
One of the most useful things I’ve learnt to do with Google Apps Script, is to email people automatically when a form is submitted. It has countless uses and in this example, we have a user requesting a private class via a Google Form. The relevant parties will receive an email which will contain a short message… Continue reading Request form – Sending automatic emails
Google Sheets Functions – QUERY
The QUERY function is in a category all on its own. It’s an extremely powerful function that will let you filter, sort, group, pivot, basically extract data from a table and present it in numerous ways. At first it can look daunting, with its own language and syntax, but once you dip your toe into the… Continue reading Google Sheets Functions – QUERY
Google Sheets – INDEX and MATCH (VLOOKUP alt.)
In this post, we’re going to look at how to use INDEX and MATCH to look up data and see how it can be more powerful than the commonly used VLOOKUP. In a previous post, we looked at how we can quickly look up tables for certain information, using the VLOOKUP function. This function is… Continue reading Google Sheets – INDEX and MATCH (VLOOKUP alt.)
Google Sheets Functions – ROUND, ROUNDUP, ROUNDDOWN
In this post we’re going to have a quick look at rounding in Google Sheets, by using the ROUND, ROUNDUP, and ROUNDDOWN functions. The syntax is very easy, we tell the function the number we want to round and then to how many decimal places. In cell A1 we have a number and in B2… Continue reading Google Sheets Functions – ROUND, ROUNDUP, ROUNDDOWN
Sheets Functions – WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH
Following on from my post on the basic date functions, let’s look at some really useful functions that work with dates, namely: WEEKDAY, WORKDAY, NETWORKDAYS, EDATE and EOMONTH, plus we’ll see an example with the CHOOSE function. With these we’ll: Find out the day of the week of a particular date Work out a deadline date Work… Continue reading Sheets Functions – WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH
Google Sheets Functions – GOOGLETRANSLATE, DETECTLANGUAGE
Lots of people know about and have used Google Translate either on their phones or on the Google website but what they often don’t know is that there is a built-in function in Google Sheets, which will allow you to translate from one language to another, and even automatically recognise the language and translate it. So,… Continue reading Google Sheets Functions – GOOGLETRANSLATE, DETECTLANGUAGE
Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR
In this post, we’re going to look at some of the basic date functions and in particular, how we can extract parts of a date or a time. We’ll cover: NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND. Example 1 – Getting the current date and time We can add the current date and… Continue reading Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR