Google Sheets Scripts - A basic helloworld example

I've written this note after an issue has been noted due to one of my scripts not working in Google Sheets.   And this is a pretty helpful way of troubleshooting any issues.   To work backwards from this. 

Troubleshooting Google Scripts

If you've got an issue you'll see "#NAME?" 

Hover over this and you'll see a report of the issue.

One way to run a test is to build up you're function from scratch .  

Let's just run a 'hello world ' example.

So let's use a function call 'SwapStringTest' .

we can call this using a SUM in the cell A1


=SwapStringTest("Hello World")


In our Scripts ( Tools > Scripts )

We'd use

function SwapStringTest(string){    return  string  }

This will simple print out 'Hello World' into our first cell.



From here I could pass every section of the function I had and found that the  STARTDATE was producing a NaN value.

to fix is I used

$start = end_date_obj.setDate(0);   // for the month prior to   $end = end_date_obj.setDate(1);




How to save for that family holiday

Actually this is more a ‘How I’m going to save’ for my holiday. Everyone’s different and theres a wealth of information online. For example; you may buy 2 coffees a day at £5 . That’d be a saving of £1300 a year, you’re almost done !

I’ve priced it up and we should be able to go on our next snowboarding holiday for around £2,000, a blog on that will follow shortly.

Here’s some things that I’m going to be following. Pretty much all this can be found at moneysavingexpert.com, so be sure to check there for more inspiration for your own savings that can be made.

Moving Bank Accounts . I’ve done this every year for the last 2 years. It’s work £150 ish. Nationwide is a good one to have . I recommended a couple of friends last year and received £200

If you want a Nationwide account then why not email me and I’ll send you a link :)

Compare all your bills against comparison sites. You’ll be able to bring one down by £10 a month for sure. That’s £120 off for the year.

Nectar ( Bonus ) Card. As I work next to Sainsburys and use BP.me I get to collect about £120 a year.

Make you own Cider / Champagne - this is a decent saving for us , I’m putting down for £200 a year.

Micro managing spending. Using Google sheets keep a close eye on what coming in and going out . Monitor the 1st month and then try and stock check and tweak to save £10 a month. I’m taking £110 off the total for this one.

Re ignite my blog and get Adsense vouchers. The aim is to get a £60 voucher for the next holiday. I did want to also reignite my YouTube channel , but you now have to fulfil a fairly high criteria to able to add advertisements.

Sale stuff on Ebay . I’ve taken off all the above calculated amounts from my £2k total and it’s left me with £21.50 a week to raise through Ebay. So every weekend I’ll try and add £20+ off stuff on there. This I fear is going to be too difficult a target to reach, as I'm a few weeks in and have hardly made a penny. So Nuumber 9 here may well come into play.

Promote other offers online a receive bonuses. Like AirBnB you get £15 if others complete a stay - plus they get £25 off . So if you’re thinking of using AirBnb then let me know ;)

Overtime - if all else fails I’ll just have to do a little bit more work ! :(

Task	Profit
Moving Bank Account	£150
Compare Bills	£120
Nectar Card	£120
Make own Cider / Champagne	£200
Micro manage spending	£110
Re ignite Blog	£60
Sell stuff on eBay	200
Promote offers for Bonuses.	£200
Overtime	£840

I'll report back with how I get on.

Mysql - using REVERSE LEFT GROUP and GROUP CONCAT to find string that match except for proceeding characters

This was the problem I was trying to solve.
MySql search to find IDs that match but some the Proceed with a Zero

This is a pretty niche little issue eh!


In one of our Software applications we have users with Payroll IDs, but some had been imported with Zero's at the beginning and some without.   Probably due to some excel settings !

What I wanted to do was write a query for this to find these issues.


To solve this I used this logic

1.    I would REVERSE the Payroll Id's
2.   That way I could match the first 6 digits on the LEFT before the we'd get to a zero !
3.   GROUP those that matched
4.  COUNT them and put the highest amounts to the top
5.  Show all of the Payroll Ids so that I could scan the list easily to see which ones could be an issue.



Here's my query.



SELECT count(*), GROUP_CONCAT(identifier) Identifiers FROM eck_payroll as p
WHERE p.identifier != 0  AND p.`state` = 1
GROUP BY LEFT(REVERSE(p.identifier),6)
ORDER BY count(*) DESC;

How to batch multiple Queries together in Sequel Pro

I’d searched for a handful of things to solve this issue, and in the end it was quite simple.
So I thought I’d note it here.
Here are some of the terms I searched

• 
‘Mac automate multiple sql commands’
• 
’sequel pro run multiple queries’
• 
‘sequel pro query window mulitple commands on run’

Here’s what the problem I was trying to solve was

I had a sql statement that was crashing, however I could run in with a LIMIT of 1000


> Click in the Query window.
> Add your query and then copy and paste the amount of rows you need. 

> Click on 'Ctrl+R'

And that’s it. 


Using OnEdit to add Date to Previous Cell in Google Sheets

Here's a nifty bit of code that will add a Date to the cell of your row when you enter data in the 3rd cell.

function onEdit(e) {

 

    var ss = e.source.getActiveSheet();

    if (ss.getName() !== 'sheet1' || e.range.columnStart !== 2) return;

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

}

Or if you want to have it on a couple of sheets you could use

function onEdit(e) {

 

    var ss = e.source.getActiveSheet();

  if (ss.getName() == 'sheet1' && e.range.columnStart == 2) {

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else if (ss.getName() == 'sheet3' && e.range.columnStart == 2){

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else if (ss.getName() == 'sheet5' && e.range.columnStart == 2){

    e.range.offset(0, -1)

        .setValue(e.value ? new Date() : null);

  }

  else {

   return; 

  }

  

}

What different voice operations are there on my phone ?

What different voice operations are there on my phone ?

I have a Samsung S9 , and I thought it may be useful to others to list the ways in which I using voice commands.

Talking to your mobile to operate it and to connect to the internet is on the rise.  It’s not only often much quicker than manually operating but it also means you can use your phone hands free.  Handy if you’re Driving.

1. Google shopping list

2. Google Routines

3. Reminders

4. Sending and receiving messages

5. Sat Nav Features

6. Asking assistant to convert something for me.

7. Chatting with Google Translate

8. Dictate something to text.  Using Voice Recorder

9.  Using Pocket to Readme a Webpage

10. Help making calls hands free calls

Google Shopping List

Just add stuff to it whenever it comes into your head 

“ok google, add XXXX to my shopping list”
"show me shopping list "

You can also have multiple contributors, so everyone in the family can add to the same list. 

> To do this just click on the person icon in the top right. 

Google Routines

This is basically a wrapper for all the things you have access to in Google Assistant.  So you can string a whole heap of stuff together.

Here’s an example for you.  On a midweek night I pick up the kids and their friends from a sports activity.  I’ve programmed run this routine when I say “Ok google I’m ready to pick up”

Google then -

1. Tells me the time

2. SMS messages the parents

3. Checks my route home and tells me how long it’ll take.
4. Read my Reminder

5. Tell me a riddle ( something to amuse the kids when the get in )

6. SMS messages my wife to say I’m on the way.

There are preset Routines and you can add as many others as you'd like

1. Open Google APP

2. Go to ‘Settings’

3. Click on Google Assistant

4. Click ‘assistant’ tab

5. Click on Routines

Reminders

Using reminders at using Google assistant as a second memory is a great for someone like me who has a terrible memory.   You can set reminders on a timer, for example 
“Remind me tomorrow morning that I need to get petrol”

You can also set a reminder on location 

“remind me in Newquay Town that I need to get Petrol “

Not only that but you can just set a reminder like 

“Remember that Mike Smiths wife is called Ann”  

and then to retrieve that you’d say 

“Remind me what was Mike Smiths wife called”

Sending and receiving messages

I’ve been sending, receiving and getting read for me ; from Gmail, SMS and WhatsApp.

"Send an Email to Mum"
"Read my last email ( from XXXX ) "
"Send a SMS message to XXXX"
"Send a Whatsapp message"
"Send a Voice message"

Sat Nav Features

“Navigate to WHEREEVER”

“Get driving directions to WHEREEVER”

Tip; if you set up your personal locations in Google Maps then you can say “home”, “work”, “Mums”, ‘the Gym” etc.  This one will impress your passengers !

Asking assistant to convert something for me.

Convert Lengths, Area, Temperature, Volume, Mass & Data.  Also ( this was a revelation to me ) calculate currencies, it even does the more random ones like Czech Koruna .

"what is 15% of 135"
"how many centimetres in 2 foot"
"How much in British Pounds in 10 thousand Czech Koruna"

Google Translate

Using Google Translate is a great way to learn some basics of a language.

“What the spanish word for XXXX”

“How do you say XXXXXX  in spanish”

Podcasts and Text to Speach

Find out what Podcast that you think will be good for you to listen to.  And then use.

“Listen to XXXXX Poscast”

“Play the latest episode of XXXXXX”

“Recommend a podcast”

If you’re looking for a Text to Speech APP then I use Pocket which is awesome.  Presave your webpages that you want it to read and then when you start your journey click on the headphones and get it read back to you.

Initiate Phone Call

This one should probably be higher of the list but you can also use your phone as a phone.

Who knew eh!

Voice control helps you initiate the calls

“call a mum at home on speakerphone”

If you’ve got any call features you use then why not let me know, I’m also looking for more ways to increase my usage of Mobile phone voice assistant options.

Most of the advice I’ve wirtten about above can from Googles documentation.

https://assistant.google.com/explore

How to Add a Hyperlink in Power BI Desktop

The documentation for that is at https://docs.microsoft.com/en-us/power-bi/power-bi-hyperlinks-in-tables

• Go to the Query editor for the Query you need to extend. 
• Go to  Add Column
• Click on Custom Column
•  New Column Name = url
• Custom column formula = "http://mysite.com" & [ Page ]
  -- where Page can be retrieved by clicking on the column name you wish from the 'Available Columns' selection on the right. 
• CLick 'OK' 
• Now Go back to the data view. 
• You can now add the 'url' column to you visual . 
• Click on the URL 'measure' on the fields display 
• Click on 'Modelling' task 
• You'll now be able to choose from the  'Data Category' dropdown and select 'Data Category: Web URL' 
• If you want to change this to a link symbol instead - you can but only in Tables
• Go to the paint roller ( formatting icon ) on the visualizations section. 
• Under 'Values' you can choice for 'URL icon' .  Slide this to ON.