A few days ago I got an update from a Google Apps Script group that I subscribe to. The poster shared a jumble unreadable single-line mess of code with a message that read something similar to:
I hired a Google Apps Script developer and they shared me this. It works, but it is unreadable. What should I do?
It looked like the poster’s code has been deliberately obfuscated or obscured to make it difficult for someone to read. The code solves the problem for the client and runs as it should. It’s just near impossible to read or edit without seeing the original source code.
Picket lines were formed in the comments section of the post and salvos ensued. It wasn’t a Reddit-level skirmish, but it was getting there before it ran out of steam.
The post and the ensuing comments did raise some good points of view that are well worth considering as a freelance Google Apps Script developer or as someone who plans to hire a developer.
Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately.
While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.
Weeds sound much more like the purview of a goat. A coding goat, perhaps 🐐. Me. I’m talking about me…yeesh!
In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.
This post is intended as a resource reference that compliments the Google Docs on the Admin SDK. Links to the Google documentation are provided throughout the post. It is worth a bookmark if you intend on using the Admin SDK a lot in Google Apps Script.
Use the contents page to navigate to what you need.
You’ve probably come to this page feeling pretty frustrated that you’ve received a link to a Google Sheet, Google Slide or Google Doc in an email or found a link to one of these documents in a website or even one of my tutorials, but you just can’t edit it. You can’t update the text and even half of the menu bar is greyed out!
It means that you have been given only View permissions to this document.
Generally, it’s because any changes in a Google Workspace Document, (Sheets, Slides, Docs) will be displayed for anyone who has access to the document. So if you add or delete something in a document, then other editors, commentators and people with view-only access will see the change in near real-time.
There are three main reasons why an owner of a document will provide View-only access to users:
1. It might have only been meant for you to read-only.
Maybe you get an email from another department from work with a new policy that they have. You don’t really need to make edits to this. You just need to read it, right?
If you think this might be the case, but you want to be able to access the document in your personal folder, you can go to File > Add a shortcut to Drive. This will save a short cut to the file in your own Google Drive to help you to better organise your documents.
You still won’t be able to edit the document, but you don’t need to.
Also, the owner or the editors of the documents can also make copies of the document after you have made the shortcut if they need to.
2. It might be an example document or template.
In my tutorials, I often provide links to templates or example documents. Here again, I only ever provide view access.
However, you can create your own copy of these documents easily, by going to File > Make a copy. You will then be prompted to move the copy into a desired folder in your Google Drive. Once done, a new window will appear in your browser. You will then have your very own copy of the document that is owned by you. You can now edit it and it will not affect the original document, because they are two separate documents.
3. The owner may have forgot to make you an editor.
Maybe the owner of the document was meant to give you edit permission but may have forgot.
Now before you get too excited, there is one thing you might want to check to save yourself some embarrassment. Go up to the top of your sheet and check to see if you are using the correct account for this sheet. You can do that in the top-right corner of the document and confirm you are using the right avatar.
Perhaps the owner has given you edit permission to the document in another one of your accounts, like a work account.
If you are in the wrong account, simply change to your correct account and check to see if you have edit permission.
If you are in the correct account and you are certain you should have edit permission, then you can select the View Only button and then click Request view access. An email will be sent to the owner and they can decide if you need edit access.
Why so much build-up to this last option? Well, you can imagine that it can be a little frustrating to get a bunch of emails from people who don’t actually need edit access.
Fortunately, there is a workaround to receiving a bunch of emails requesting edit access unnecessarily. Check out the tutorial below:
NOTE! Clicking the links above will automatically download the file described.
This quick tool allows you to do just that. It works with any file like:
Images e.g. *.png *.jpg.
Documents e.g. *.pdf *.doc.
Zip files e.g. *.zip *.7z.
Pretty much anything that isn’t a Google Workspace file like a Google Sheet, Google Doc, Google Slide.
Table of Contents
Grab and set up the file
Find the file you need to set up for auto-download.
Righ-click the file.
Select Get link.
Ensure the settings says, Anyone with link can view.
Select Copy link.
Create the link
*Wait for the converter to load.
Do no harm!
Yes, I guess this could be used for downloading some nefarious content. This little tool is designed to provide a convenience for you and the people you want to share your files with. Please consider other people when preparing a tool for them. Even a quick heads up in your own content might be a good idea:
Note! File will download automatically from Google Drive.
Note! Clicking this file will download it automatically.
What about my Google Workspace Files?
Not directly with this tool, I’m afraid. The simple answer is Google Workspace files are designed to be in Google Drive and online. Much better for you to share the file publically and then provide a link to the file.
If you are intending on providing your Google Workspace files or folders for your users to download and use offline anyway then you could download the in advance. This will cause the Google Workspace files to be exported into an offline format. Then you can drag that converted file and put it back in your drive and share it. You will be able to complete the steps above then.
Also, if you are downloading an entire folder it will export it files into usually Microsoft formats for you automatically and save the entire document in a zip file. Then you can drag that zip file back into your Google Drive and convert with the above too.
It’s always nice to a have a bit of a look back at the previous year to see how you went, what you’ve achieved, what you can work on and where you want to go in the future. 2020 was a rough one for everyone and it was no different for this goat 🐐. Some extremes of this year pushed me and my wife to some pretty major life changes. Some hard; some very, very positive.
The executive summary is like this:
My wife and I moved countries.
I left my managerial job to focus on Yagisanatode and developing fulltime.
I revealed the man behind the goat.
My site has exploded in views ( I am incredibly grateful ❤)
I’ve produced more content than ever.
I became a Google Developer Expert in Google Workspace.
The jokes are still bad.
The website got two overhauls.
I started my first Youtube Series.
Read on to get the full details:
Table of Contents
My main goal was to reach 1 million unique visitors by the end of 2020. Why is this important? Well, I think it is just a pretty good metric to show that my work is helping people, people are sharing my tutorials on sites like support.google.com and Stack Overflow, and the posts are clear enough for Google search to recognise that I am making good content.
So did I reach 1 million unique visitors? No, but frustratingly almost. As of lunchtime on the last day of 2020, I got to 981,988 visitors since my first ever post back in October of 2017.
For me, that is still pretty awesome, and I am truly thankful for all the people who take the time to visit my site and hopefully find something useful to help them with their own project.
Here are few more all-time stats since I first started:
Total posts: 135
Total views: 1,350,035
Total visitors: 981,988
That’s an average of 1.37 page views per person.
I now have 90 legendary email subscribers that receive an email every time I post and 320you-beaut followers overall.
This year, I have published 51 posts (up 13 posts from last year). 27 of these posts have been a part of the Google Sheets Basics Youtube Series. I wrote 18 posts on Google Apps Scripts, 5 on Google Sheets and 1 posts on Google Slides.
I wrote a total of 94,891 words this year (up 6,197 words from 2019) and on average wrote 1,861 words per post.
The most popular post written this year was a Google Apps Script post:
This year’s visits and views have skyrocketed too:
Views: 816,785 – up 372,419 views from 2019
Visitors: 597231 – up 278262 visitors from 2019
Covid-19 made it an incredibly busy first half of the year in my day job. In many months I had to reduce my usual 2 posts a month to only one, just to keep up with the demands of my full-time job. Much of the increase in posts has been since October, where I have produced up to 16 video posts a month and up to 5 long-form posts a month.
Oh. I also got my first DD0S attack (You know you really made it then 🤣). Fortunately, My WebHost GreenGeeks were all over it and let me know and helped me improve the security of my site.
This year saw some significant upheavals to the Yagisanatode website. I have improved the site security and made two runs at improving my Site Speed some moderate success. I’ve also worked on improving the sites layout to make it more browsable.
Site speed still needs a lot of work though, though I have made use of better caching and other plugins to improve the loading speed of the website. My biggest issue is my use of gifs and images. I think visual cues are important for you so you can see what is happening in the process. However, now I am starting to change to using short looped mp4’s instead of gifs and using an expand-box for larger images to reduce load speeds.
Mobile performance is still a pain-point for me and I am really struggling to improve this. Hopefully, with time, I will figure something out.
My website’s layout has been really bugging me, and while I have made a lot of changes this year, I still have more work to do in 2021.
The first run at the Google Apps Script and Google Sheets pages
The first clue that I needed to make some improvements was some feedback from family (Thanks Earnie and Kerry), who also happened to be academic administrators. They found it challenging to find a learning progression in my posts in a sort-of course structure.
I responded to this by creating unique Google Apps Script and Google Sheet pages where not only did I have a list of lastest posts but also ordered some posts into courses and categories of ability. I also provided resources for further study.
The Post Grid
Around April I got a great email from a reader who suggested that I move from displaying single-lines of posts with a rather long preview to a grid of posts containing the feature image, title and tags.
I included this in the Google Sheets and Google Apps Script pages of the site. The bump in browsing habits suggests that it seemed to have helped user retention which might mean that I am helping you find what you need better.
Updating the Homepage
In December, I finally managed to get around to updating the homepage. Before, there was the old list posts, one under the other with a little bit of a preview of the writeup.
My wife helped me to redesign the home page so that it made browsing more accessible and highlighted recent posts, while also briefly showcasing what the site is about. It is one-thousand times better than what it was, but there is still room for improvement.
My favourite part of the whole process was creating the feature buttons.
Help! I would really love your advice on how to improve the layout of this website. If you have any suggestions, liked or loathed the changes, please share your thoughts in the comments below.
Other Business Accomplishments
Becoming a Google Developer Expert
Without a doubt, my proudest moment this year was being accepted as a Google Developer Expert or GDE for Google Workspace. Essentially this is recognition from Google for the work I put into supporting the community.
It also gives me the opportunity to give feedback on Google Workspace products, but the biggest bonus is the community you become a part of. It’s hard to describe how amazing these people are. Their support and dedication is something I greatly admire.
A huge thank you goes out to Sourabh Choraria for recommending me to the Google Dev Advocate team. Martin Hawskey for the first GDE interview and Dev Advocate, Charles Maxam for accepting the very first goat as a GDE.
While I had a tiny YouTube presence to help demonstrate my two Google Workspace add-ons, it wasn’t until June this year that I decided to produce a YouTube series of Google Sheets shorts that walk viewers through most of the tools that Google Sheets provide. The videos cater for the absolute beginner and go into my usual pedantic detail on each topic.
Most of the videos have a table of contents so you can quickly get to what you need and move on with your project.
You will also start to see these videos pop up in some of my long-form posts to help with instructions.
There is everything from styling your Google Sheet, to filtering data and printing and exporting.
There are still 15 more videos in this series. You can check out the series below:
Updated Sections to Sheets
This year, I didn’t get around to publishing any more Google Workspace Add-ons. However, I did make some minor changes to Sections to Sheets an Add-on where you can select a column of data and separate it into individual Sheet Tabs categorised by the values in your chosen column.
Don’t worry. It’s totally FREE!
After some feedback from users, I have slightly improved the performance of the processing of the values into separate sheet tabs and taken the restriction off only having single-item categories.
After 5 years of living in the Middle East, we travelled back to Australia at the end of June into two weeks of hotel quarantine before emerging into a mask covered Sydney.
I had an increasingly worsening back injury. So our plan at the time was to hold out in Australia and get my back sorted out before returning back to work overseas.
Leaving the Job
Unfortunately, the injury was a little worse than expected. Also, COVID wasn’t lightening it’s grip on the world any time soon. On top of that, I was facing exponentially increasing demands from work and the request to return to the Middle East without my wife.
Mrs Yagi and I are joined at the hip so with all this in mind, we tended our resignations and I commenced the handover to my replacement.
I’m sad to see the job go, I created a lot of administrative automation and processes (in Google Workspace 😉) that I can say that I am proud of and save my staff unnecessary work. Nevertheless, the position is in good hands and I am sure that my replacement is doing a tip-top job.
Yagisanatode All The Way!
After making the decision to leave my job, I really don’t think I hesitated in throwing myself at Yagisantatode.com full time. I love developing automation solutions in Google Workspace and teaching people how to reduce their administrative burden.
Running a business and having a side hustle are very different things, however. There is registering a business, doing taxes again, meeting clients, optimising the balance between earning a survivable income and providing great content for readers and a panoply of so much more.
So from October to the end of the year, I spent the rest of my time moving Yagisanatode from a weekend hobby business to a fulltime enterprise. I am learning a lot on the way, making mistakes and getting better (I think). I have some changes and directions I want to test in the coming year to figure out how I am going to best settle into this new life.
I think I can fill some of the gaps in the Google Workspace education world and be unique enough to support, buffer and encourage my fellow Google Workspace educators. One of the things I love about our particular community is the support and encouragement. There is no aggressive competition but rather a genuine desire to enrich the knowledge pool and support one and another.
The Man Behind the Goat
And at last, one of the most fun things I got to do was update my About page and disclose who I was. To be honest. It wasn’t much of a secret if you did a bit of digging (and a few did 😂).
So, yeah… Hi! I’m Scott. I’m from Australia. Here’s me pic, mates:
Slightly less goaty than you imagined?
So much has happened at, I feel, the right time for Google Workspace in 2020. It really took up the slack with supporting educators scrambling to provide online classes with Google Classroom and quickly expanded the capabilities of Google Meet, their video conferencing software, and then provided it for free.
I know, hand on my heart, Google Workspace saved me this year, and for that, I want to thank all the Googlers involved in developing all the Google Workspace tools.
Not only that, Google Workspace had come up with some amazing updates and new products. These are my two favourites for 2020:
Google Apps Script – New IDE: The end of the year equally wrapped up with a bang with the release of the much anticipated new IDE for Apps Script. It is much more modern and userfriendly with a bunch of cool tools.
With the move from a weekend side-gig to a fulltime job, you can guess that I have a lot of plans ahead for the coming year.
Plans & Goals
I am currently in the process of developing a web project. Is it Google Workspace related? No, not really, but there are a few things I want to achieve with this project:
Improve my front-end dev chops.
Get familiar with another Google product, Firebase.
And finish up with a website that people would like to engage in and generate a little passive income.
Once I have finished up with the project, I plan on creating two Google Workspace related courses that I think will be very relevant for 2021. The plan is to release the first one by March.
Posts and Videos
Long-form tutorials on Google Apps Scripts, Google Sheets and other Google Workspace related topic will continue with a minimum of 2 a month. I will be testing out a few approaches to make them more accessible and succinct. Please bear with me while I work this out and if you have any feedback, don’t hesitate to let me know.
The Google Sheets: Beginners video series will continue for up to 44 videos. Then I have two more short video series that I hope to push out throughout the year for you.
I would really love to do some live talks. Either online or at a meet. It’s just one of those goals that, although terrifying, is important to accomplish.
I’d love to do some talks in rural towns in Australia and introduce them to Google Workspace. I think it would benefit small and medium rural businesses by broadening their reach and saving them a bunch of time and money.
A little more time with loved ones and exploring
I do tend to give everything I’ve got and work myself to the bone to get things done. This year it affected my health. So a major goal for this year is to dedicate moments to reflect, relax and spend time with my best mate, Mrs Yagi.
To work on
If anything has come out of producing YouTube videos it is that I very much have the stereotypical Aussie male mumble. This year, I need to really improve my speaking skills and strive towards a one-shot recording of my short videos. Wish me luck.
My long-form posts have started to get carried away. I think while this year, that I really started to delve into more in-depth multi-part problems in Google Apps Script, but I do think I am slipping into a bit of a waffle in my post. This is wasting my time writing the post and your time reading it.
One of the challenges of my writing is to try and write to as broad an audience as possible. Unfortunately, with that comes more explanation. I do encourage you to simply read what you need and skip the rest, but I do think I can go too far in my explanations. Time to trim the fat a little and develop some tools to help make explanations clearer but also more concise.
Meeting more people in the community
The social restrictions that were created by COVID created the perfect hermit environment for me. However, as much as I love and embrace solitude, I know that the feedback loop of more isolation is probably not the best for someone with reclusive tendencies.
I do feel myself losing what social skills I had.
I do enjoy people’s company and more so, learning from them and sharing ideas. Just the thought of the initial engagement terrifies me a little. But it is time to toughen up and bite the bullet and get out there and reach out to people online and, when it’s safe to do so, in person.
I would love to go to some developer meetups and cons around Australia or be apart of any online conventions or meets. Please let me know if there are any around that you think might be interesting. I might be a bit clumsy in my introductions, have no idea what to say or ask and may stand ominously in the corner, but I am interested in you I can guarantee.
Finally, I hope to engage more with my readers in the comments of this site and on Twitter. I think the start of the year I could not reply as much as I wanted to due to work and that made me feel I was letting you down.
That’s a wrap for 2020. Thanks for following along on this wild ride of a year. I appreciate everyone who has taken the time to read my tutorials. I thank all the people I have engaged within the comments, via email, on Twitter over at the Google Groups Community and the GDEs.
Finally, a massive thank you to my wife for her support, love, advice and hugs.