<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jasper Duizendstra on Medium]]></title>
        <description><![CDATA[Stories by Jasper Duizendstra on Medium]]></description>
        <link>https://medium.com/@duizendstra?source=rss-b87cf4ebf968------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*7OTYqdcDPVOKJ9yTkKQa-Q.png</url>
            <title>Stories by Jasper Duizendstra on Medium</title>
            <link>https://medium.com/@duizendstra?source=rss-b87cf4ebf968------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:49:25 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@duizendstra/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Google Apps Script deployment with Cloud Build]]></title>
            <link>https://medium.com/google-cloud/google-apps-script-deployment-with-cloud-build-d9d0abf7dcf8?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/d9d0abf7dcf8</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[continuous-deployment]]></category>
            <category><![CDATA[google-cloud-build]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Sun, 13 Dec 2020 20:44:25 GMT</pubDate>
            <atom:updated>2020-12-13T20:44:25.884Z</atom:updated>
            <content:encoded><![CDATA[<p>Google Apps Script can go way beyond a simple script that solves a problem. In this article I will showcase the way I deploy Google Apps Script code in an Enterprise environment. I will share the actual build file and the necessary steps and tools to reproduce my workflow. This approach does require Google Cloud Platform knowledge and a credit card. The instructions are high level, this article is not a codelab. If you get stuck, leave a comment and I will try to assist and update the article.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hpGdwgTJIUJhiYwU" /><figcaption>Photo by <a href="https://unsplash.com/@davidg707?utm_source=medium&amp;utm_medium=referral">David Gilbertson</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>When I use Google Apps Script to solve a problem I choose between three workflows using:</p><ul><li>the, <a href="https://workspaceupdates.googleblog.com/2020/12/google-apps-script-ide-better-code-editing.html">recently updated</a>, Apps Script IDE.</li><li><a href="https://git-scm.com/">Git</a>, <a href="https://github.com/google/clasp">clasp</a> and the <a href="https://shell.cloud.google.com/?show=ide&amp;environment_deployment=ide">Cloud Shell Editor</a></li><li><a href="https://git-scm.com/">Git</a>, <a href="https://github.com/google/clasp">clasp</a>, <a href="https://shell.cloud.google.com/?show=ide&amp;environment_deployment=ide">Cloud Shell Editor</a>, <a href="https://cloud.google.com/source-repositories">Cloud Source Repositories</a>, and <a href="https://cloud.google.com/cloud-build">Cloud Build</a></li></ul><p>The new Apps Script IDE is great. However it does lack some of the features that an Enterprise environment dictates, like version control and Continuous Deployment (CD). The <a href="https://cloud.google.com/blog/products/application-development/introducing-cloud-shell-editor">Cloud Shell Editor</a> is a huge step forward. I strongly advise to try it at <a href="https://ide.cloud.google.com/">https://ide.cloud.google.com</a>. Clasp and Git are already installed in the terminal.</p><p>But we are talking Enterprise level, so why keep things simple. Actually, using CD does a lot of advantages. When the CD pipeline is in place it becomes easy to deploy the script, separate the responsibilities and support multiple versions and implementations of the code. Using Git dictates version control and items like <a href="https://keepachangelog.com/en/1.0.0/">keep a changelog</a> and <a href="https://www.makeareadme.com/">Make a README</a> makes sense in such a structure.</p><p>For me the most important advantage of using CD is the mindset that it enables. It provides an environment where I feel confident and safe to create small incremental changes in my applications. The podcast: <a href="https://cd.foundation/podcast/">The Pipeline: All things CD &amp; DevOps Podcast by The CD Foundation</a>, does a nice job of explaining that.</p><h4>Hands on, how do we set it up</h4><p>I usually start with the repository <a href="https://source.cloud.google.com/">https://source.cloud.google.com/</a>. This will require a GCP project with billing enabled. Once you created your repository, fire up the <a href="https://ide.cloud.google.com/">https://ide.cloud.google.com</a>.</p><p>Open a terminal and create a local clone of your repository. Use one of the three options to clone the repository. Once the repository is cloned you can open the new directory as a workspace.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/851/1*C4zLoJPm7Va2IwdlDlRluw.png" /><figcaption>The repository screen</figcaption></figure><p>At this point you should have an empty repository open in the workspace. Next step is to fetch the clasp credentials. We need these credentials in the cloud build. Type the command below and follow the instructions. Authorize with the account that you want to use in the cloud build.</p><pre>clasp login --no-localhost</pre><p>If all goes well the credentials are saved in the file ~/.clasprc.json. We will need that later.</p><h4>The Google Sheet</h4><p>It is possible to create a script with clasp itself, however in this case we will keep it simple and use a Google Sheet. So at this point create a Google Sheet and open the script editor. Find the script id and type the following command in the terminal:</p><pre>clasp clone [script id]</pre><p>Create the .gitignore file and add .theia and .clasp.json. Next add the example code from <a href="https://developers.google.com/apps-script/guides/menus#custom_menus_in_google_docs_sheets_slides_or_forms">Custom menus in Google Docs, Sheets, Slides, or Forms</a> to the Code.js file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*_7YG8HFHa9fQoONTTni17Q.png" /><figcaption>This is what you should see in the editor</figcaption></figure><p>Now we need to create the cloudbuild.yaml file:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bef5c6f87c5d7cc661ca403947dbfce1/href">https://medium.com/media/bef5c6f87c5d7cc661ca403947dbfce1/href</a></iframe><p>All the code is in place now. In a real live deployment do consider <a href="https://keepachangelog.com/en/1.0.0/">keep a changelog</a> and <a href="https://www.makeareadme.com/">Make a README</a>. Now you can commit the code. Git will ask you to do some configuration:</p><pre>git config --global user.email &quot;git@example.com&quot;<br>git config --global user.name &quot;Git Example&quot;</pre><p>Push the code and check if the code is in the repository</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/813/1*PIWh7Qm9f4vSqo3o6BoOmw.png" /></figure><p>Now we can start the build. Go to <a href="https://cloud.google.com/cloud-build">Cloud Build</a> and create a new <a href="https://console.cloud.google.com/cloud-build/triggers">trigger</a>. Give the trigger a name and connect to the correct repository. For now use the following settings:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/547/1*N_Q-SWPlUUSD4WPqKvwcBA.png" /></figure><p>Also add the substitution variables, use your own script id.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/557/1*8nQrMa-EO1ztyeCI49EOaA.png" /></figure><p>In this step we will store the token for clasp. Go to the <a href="https://console.cloud.google.com/security/secret-manager">Secrets Manager</a> and create a new secret clasp_id. The value of the secret is the content of the .clasprc.json file.</p><pre>cat ~/.clasprc.json</pre><p>Cloud build needs access to the secrets, find the service account and assign the role Secret Accessor in the <a href="https://console.cloud.google.com/access/iam">AIM</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/490/1*UyqSvZ30v7C3lAWVQp65xg.png" /></figure><h4>Take a breath, almost there</h4><p>There are a lot of moving parts in this setup. We have a Google Sheet, a Git repository, a build trigger and a Cloud secret. The trigger will trigger a build when a tag like v1.0-prod is pushed to the Git repository. The build will execute the following steps:</p><ul><li>authenticate clasp by pushing the cloud secret value into the .clasprc.json file</li><li>create the .clasp.json file with the script id</li><li>install clasp</li><li>push the code to the script</li></ul><p>So if we type the following in the terminal:</p><pre>git tag v1.0-prod<br>git push origin v1.0-prod</pre><p>If all goes well you can see the build</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/730/1*KbDFDuFsNDS3Fz80Oop-iw.png" /></figure><h4>Wrapping up</h4><p>This might seem a lot of work just to maintain some scripts. If it is just a simple script, by all means use the great new editor. But what if you are not allowed to access the sheet? Or if you want to rollback a change? Or if you want to deploy to 25 sheets simultaneously?</p><p>This solution is meant for situations where you need a process in place that ensures a consistent deployment process. If required it also makes the separation of responsibilities easy. Only the repository and the build trigger need to be in the same project, assuming you use Cloud repositories. You can use authenticate with different accounts by storing multiple clasp tokens in the secrets.</p><p>I hope this method has given some insight in the options and power of the combining Google Apps Script and GCP. If you have any questions, or if you got stuck while implementing the example, please leave a comment.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d9d0abf7dcf8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/google-apps-script-deployment-with-cloud-build-d9d0abf7dcf8">Google Apps Script deployment with Cloud Build</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Google Apps Script Example Project]]></title>
            <link>https://javascript.plainenglish.io/apps-script-example-project-get-the-number-of-emails-with-a-specific-label-d7e1fb2e998b?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/d7e1fb2e998b</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[javascript]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[google]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Sun, 13 Dec 2020 09:37:36 GMT</pubDate>
            <atom:updated>2020-12-13T14:02:37.378Z</atom:updated>
            <content:encoded><![CDATA[<h4>Get the number of emails with a specific label</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TqGCs7TYeRPFZoB5" /><figcaption>Photo by <a href="https://unsplash.com/@andreasdress?utm_source=medium&amp;utm_medium=referral">Andreas Dress</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Software development is hard. This tweet illustrates how professional developers sometimes see their job:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/596/0*dKYifVhGB0BPeNGJ" /><figcaption><a href="https://twitter.com/catalinmpit/status/1327533237065969666">https://twitter.com/catalinmpit/status/1327533237065969666</a></figcaption></figure><p>It does not always have to be that way. In this article I will show how a <em>citizen developer</em> can quickly move from idea to a working solution using Google Apps Script.</p><p><em>A citizen developer is a user who creates new business applications for consumption by others using development and runtime environments sanctioned by corporate IT. In the past, end-user application development has typically been limited to single-user or workgroup solutions built with tools like Microsoft Excel and Access. However, today, end users can build departmental, enterprise and even public applications using shared services, fourth-generation language (4GL)-style development platforms and cloud computing services. (</em><a href="https://www.gartner.com/en/information-technology/glossary/citizen-developer"><em>https://www.gartner.com/en/information-technology/glossary/citizen-developer</em></a><em>)</em></p><p>For me a <em>citizen developer </em>can be anybody who found Google Apps Script. This article is written from that perspective. I will not discuss all the things you need to do to make your code robust and maintainable. You write the code and you are responsible for the usage and lifecycle.</p><h3>From the use case to an application</h3><p>The first step is to understand the use case. This means asking the right questions. The perspective is automating something that can be done manually. For the citizen developer it is very important to start from here.</p><p>In the example use case we would like to record the number of the emails that have a certain label in a certain period. This information should be recorded in a Google Sheet.</p><p>If we unpack this a bit we get:</p><ol><li>Count the number of emails that have a label and are received in a period</li><li>Record this information in a Google Sheet</li></ol><p>The first step is reproducing the steps manually. The only challenge here is to count the emails. So we make sure we can do this manually:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/0*ZsbQ_C-_aeRa3qAA" /><figcaption>Gmail screenshot of the search window</figcaption></figure><p>Once we made sure we can do all steps manually we need to translate it to code. This is where the Google Apps Script documentation helps (<a href="https://developers.google.com/apps-script">https://developers.google.com/apps-script</a>).</p><p>In our case we can push the information from Gmail or pull the information into a Google Sheet. To get a better idea we can take a look at the “Extend the Google Workspace” section in the guides <a href="https://developers.google.com/apps-script/overview">https://developers.google.com/apps-script/overview</a>. Gmail is not mentioned there, however Google Sheets is.</p><p>Now we have a few choices. We either read the whole document (<a href="https://developers.google.com/apps-script/guides/sheets">https://developers.google.com/apps-script/guides/sheets</a>) and try to understand or we skim the document and try to find something we can start with. I usually pick the last option. In this case the first steps are:</p><p>“To create a bound script, select <strong>Tools &gt; Script editor</strong> from within Google Sheets.”</p><p>And some code that will write something to the sheet:</p><pre>function addProduct() {<br>  var sheet = SpreadsheetApp.getActiveSheet();          <br>  sheet.appendRow([‘Cotton Sweatshirt XL’, ‘css004’]);<br>}</pre><h4>Hands on, the sheet</h4><p>So we create a sheet and open the script editor. Or you can create a copy of this sheet: <a href="https://docs.google.com/spreadsheets/d/19N5nkEi-1q1i3QLRhYsZ6EULv0Uizr27E87kMCLKyKI/edit#gid=0">Article Example project — Measuring the number of emails label</a></p><p>When you have the editor open, paste the code in the editor. And when we run the code we get a result:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/675/0*z1D6BBJk-NLw7EPP" /></figure><p>Let’s make this a bit more like the end result:</p><pre>function countEmails() {<br>  var sheet = SpreadsheetApp.getActiveSheet();<br>  sheet.appendRow([‘apps-script’, 155]);<br>}</pre><p>When we run the code we see the label and a count.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/717/0*81xD2byCFk8-Gi3m" /></figure><h4>Hands on, getting the counts</h4><p>The next step is to get the actual counts from the mail. This requires a bit of knowledge of the Services in Apps Script. Open the Google Workspace Services section on the references page (<a href="https://developers.google.com/apps-script/reference">https://developers.google.com/apps-script/reference</a>). We would like to do something with Gmail, so let’s open the GmailApp page.</p><p>This is a tricky part. Here we need to map the manual action to Apps Script code. This can take some time to find. In our case we do search and we can use: <a href="https://developers.google.com/apps-script/reference/gmail/gmail-app#search(String)">https://developers.google.com/apps-script/reference/gmail/gmail-app#search(String)</a></p><p>We already have the query so we can try this:</p><pre>function myFunction() {</pre><pre>  var threads = GmailApp.search(‘label:apps-script after:2020/10/18  before:2020/11/2 ‘);</pre><pre>  console.log(threads.length)</pre><pre>}</pre><p>Make sure you replace my query with your own working query</p><p>There are two new things, console.log() and threads.length(). Trust me on this one and run the code again.</p><h4>Hands on, bringing it together</h4><p>Our steps were</p><ol><li>Count the number of emails that have a label and are received in a period</li><li>Record this information in a Google Sheet</li></ol><p>That translates to:</p><pre>function countEmails2() {</pre><pre>  var threads = GmailApp.search(‘label:apps-script after:2020/10/18 before:2020/11/2 ‘);<br>  var sheet = SpreadsheetApp.getActiveSheet()</pre><pre>  sheet.appendRow([‘apps-script’, threads.length]);</pre><pre>}</pre><h3>Wrapping up</h3><p>In this article I discussed the steps that a <em>citizen developer </em>can take to quickly automate a task. The aim is not to write beautiful maintainable code, but to break down the manual task into steps that can be programmed. This way the process remains easy and manageable.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d7e1fb2e998b" width="1" height="1" alt=""><hr><p><a href="https://javascript.plainenglish.io/apps-script-example-project-get-the-number-of-emails-with-a-specific-label-d7e1fb2e998b">A Google Apps Script Example Project</a> was originally published in <a href="https://javascript.plainenglish.io/">JavaScript in Plain English</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Overview of the Apps Script dashboard]]></title>
            <link>https://medium.com/google-developer-experts/overview-of-the-apps-script-dashboard-97915324e8d2?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/97915324e8d2</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[dashboard]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Thu, 10 Dec 2020 18:52:02 GMT</pubDate>
            <atom:updated>2021-01-08T17:46:16.707Z</atom:updated>
            <content:encoded><![CDATA[<p>The Apps Script dashboard lets you manage and monitor your Apps Script projects. This is the first sentence in the documentation about the Apps Script dashboard. It describes the high level purpose of the Apps Script dashboard, but what does that mean in real live environments. In this article I will describe some of my use cases for the Apps Script dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*gySw6T8-Q31Y3dKy" /><figcaption>Photo by <a href="https://unsplash.com/@valeon?utm_source=medium&amp;utm_medium=referral">Mitchel Boot</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Opening the Apps Script dashboard is usually the first step I take whenever I start working in a new environment. I work for multiple companies and I have a lot of Google Accounts. It is great to see all the projects in the “All Projects” section. This will immediately give you an idea of what kind of projects you have access to. This might sound obvious but when I opened the dashboard of my oldest account and sorted on the last modified date I saw a script last modified on Dec 2, 2010.</p><h3>Projects overviews</h3><p>The “My Projects” and “Shared with me” are nice subsets of the projects. I do miss a better filter and sorting on the owner would also be great. If you hover over a project you can open the project information by clicking on the information icon. The information page provides an overview of the script and container if it is a container bound script.</p><p>Starring projects is one of the most useful features for me. Hover over a project and use the three dots to open the menu and star the project. In the screenshot below I have starred five projects.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*7OINCbcyS4lQEcyd" /><figcaption>This overview shows all the relevant information for the starred projects.</figcaption></figure><p>Executions and triggers</p><p>The “My Triggers” and “My Executions” sections show your executions and triggers. This can be confusing because there can be other executions and triggers. Open the project itself if you want to see all the executions and triggers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vqJtXdIFdSE-QlfP" /></figure><p>You can use the filters to limit the rows in the overview:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*svnQqT2QBArP1Z4I" /></figure><p>In the “My Triggers” section you can see all your triggers. Hover over the trigger and use the pencil icon if you want to edit the trigger. The trigger can be deleted in the menu that opens with the three dots.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/428/0*KJkhEQtEbloqTbjd" /></figure><h3>The rest of the options</h3><p>In the “Getting Started” you will find additional information and some starter projects. The “Settings” section only contains the option to enable the Google Apps Script API. And finally the “Service Status” section links to useful sections in the Guides.</p><h3>Wrapping up</h3><p>In this article I have discussed the “quirks and features” of the Apps Script dashboard. The Apps Script dashboard provides a bird’s-eye view of all the Google Apps Script script you own or have access to. It also enables you to dive right into the project information.</p><p>For me the Apps Script dashboard is the first thing I see when I start working on a new project or when I want to get familiar with the Google Apps Script scripts available for an account.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=97915324e8d2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-developer-experts/overview-of-the-apps-script-dashboard-97915324e8d2">Overview of the Apps Script dashboard</a> was originally published in <a href="https://medium.com/google-developer-experts">Google Developer Experts</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to write a custom function library for Google Sheets]]></title>
            <link>https://medium.com/google-cloud/how-to-write-a-custom-function-library-for-google-sheets-87f62729e684?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/87f62729e684</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[google-sheets]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Wed, 09 Dec 2020 16:08:15 GMT</pubDate>
            <atom:updated>2021-04-10T06:58:48.816Z</atom:updated>
            <content:encoded><![CDATA[<p>In this article I will showcase one of the many ways you can customize your Google Workspace environment. We will implement two custom functions in Google Sheets. With these functions as an example I will demonstrate how to use Google Apps Script Libraries to organise and distribute your solution. There are multiple ways to distribute your Apps Script code within the Google Workspace, and this article will help you decide if this library method is right for you.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9CNRoTTjtNO7-1cT" /><figcaption>Photo by <a href="https://unsplash.com/@gabons?utm_source=medium&amp;utm_medium=referral">Gabriel Sollmann</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Custom functions are a great way to address specific use cases in Google Sheets. In this case I will focus on how to maintain the functions, not on the functions themselves, therefore I based my example functions on the examples in the <a href="https://developers.google.com/apps-script/quickstart/custom-functions">Quickstart: Menus and Custom Functions | Apps Script</a>.</p><h3>Container bound scripts</h3><p>Google Sheets use <a href="https://developers.google.com/apps-script/guides/bound">Container-bound Scripts | Apps Script</a>. This means that the script comes with the sheet and if you make a copy of the sheet you will automatically get a copy of the script. This makes it difficult to maintain the custom functions in the script.</p><p>Moving the functions into a library solves the maintenance problem. However it introduces a new problem. Once the functions are in the library they are not visible for the Google Sheet. To solve this we need to bring the functions back into the container bound script of the Google Sheet.</p><h3>Why use a library to distribute your functions</h3><p>With a library you will get:</p><ul><li>Versioning of your code</li><li>Control over development or production versions</li><li>Easy distribution of your code</li></ul><p>With libraries you are also making a tradeoff between ease of use and maintenance. The main drawbacks of the solutions are:</p><ul><li>Less intuitive function names</li><li>Help information takes is difficult to implement</li></ul><p><a href="https://developers.google.com/apps-script/guides/libraries">Libraries | Apps Script</a> describes how to create a library. The example code below uses the construction described in this article <a href="https://medium.com/swlh/structure-and-simplify-your-google-apps-script-with-v8-37e29d3e1edf">Structure and simplify your Google Apps Script (with) V8</a>.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/98cfc9b05385ee57e6c0db9490fd8146/href">https://medium.com/media/98cfc9b05385ee57e6c0db9490fd8146/href</a></iframe><p>In the example we moved the two custom functions into the customFunctions object. These functions are placed into the allFunctions object.</p><p>The getFunctions function returns a function based on the name and passes the parameters to the custom function.</p><p>The next step is to add the library. In the example below the name of the library is sheetFunctions. Once the library is available we can make the functions available.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2aad6892d7e0b37a31f2bdc49f91bf07/href">https://medium.com/media/2aad6892d7e0b37a31f2bdc49f91bf07/href</a></iframe><p>In the example above we implemented the functionality in two ways. Row one to seven enables this usage of the custom functions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/272/0*Y2v3_NvLUKlFXisW" /></figure><p>Row nine to eighteen will make the help information available. This eliminates the drawback of using a library, however it makes it difficult to introduce new functions or change the parameters of the function. The implementation of the function remains in the library.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/399/0*eMvKF_FE2BzpzjMu" /></figure><p>In this article I have shown how to move custom functions to a library.</p><p>There are other ways to maintain custom functions in a Google Sheet, implementing an add-on (<a href="https://developers.google.com/gsuite/add-ons/overview">Extending Google Workspace with Add-ons | Google Workspace Add-ons</a>) is also a great way to maintain and distribute your custom functions. Using a library can help when you don’t want to use an add-on and still want to be able to maintain your code after distribution.</p><p>Read the guides <a href="https://developers.google.com/apps-script/guides/sheets/functions">Custom Functions in Google Sheets | Apps Script</a> and <a href="https://developers.google.com/apps-script/guides/libraries">Libraries | Apps Script</a> if you would like to dive deeper into the functions and libraries.</p><p><strong>Resources</strong>:</p><p><a href="https://developers.google.com/apps-script/guides/sheets/functions">Custom Functions in Google Sheets | Apps Script</a></p><p><a href="https://developers.google.com/apps-script/quickstart/custom-functions">Quickstart: Menus and Custom Functions | Apps Script</a></p><p><a href="https://medium.com/swlh/structure-and-simplify-your-google-apps-script-with-v8-37e29d3e1edf">Structure and simplify your Google Apps Script (with) V8</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=87f62729e684" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/how-to-write-a-custom-function-library-for-google-sheets-87f62729e684">How to write a custom function library for Google Sheets</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to get the most out of the new Google Apps Script debugger & logging]]></title>
            <link>https://medium.com/google-developer-experts/how-to-get-the-most-out-of-the-new-google-apps-script-debugger-logging-8996b6a06cc6?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/8996b6a06cc6</guid>
            <category><![CDATA[debugging]]></category>
            <category><![CDATA[google-workspace]]></category>
            <category><![CDATA[javascript]]></category>
            <category><![CDATA[google-apps-script]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Tue, 08 Dec 2020 12:42:02 GMT</pubDate>
            <atom:updated>2021-01-08T17:46:30.446Z</atom:updated>
            <content:encoded><![CDATA[<p>Building and maintaining code can be hard. There are multiple phases in the livecycle of the code, each phase with its own challenges. Being able to easily retrieve information about the execution of your code in each phase is key. This is where the new Google Apps Script IDE debugging and logging features can help. In this article I will show you what you can do with the new debugging and logging features.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NTpqXdJo1ycPCF5d" /><figcaption>Photo by <a href="https://unsplash.com/@timothycdykes?utm_source=medium&amp;utm_medium=referral">Timothy Dykes</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>A simple Google Apps Script is used to walk through the powerful features of the debugger and logging. In the final part of the article we will take it a step further and implement the Google Cloud logging module. After reading this article you will have a good understanding of the options to debug and monitor your Apps Script applications.</p><h3>The debugger</h3><p>The debugger enables you to walk through the code step by step. When in debug code the function will run until a debugger statement, or a breakpoint is encountered. The debugger statement only has an effect when the code is run in debug mode.</p><p>I will use the following code to showcase the features of the debugger:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2236cdc1614c2894251b3bd193f8ea00/href">https://medium.com/media/2236cdc1614c2894251b3bd193f8ea00/href</a></iframe><p>The code can be started by clicking on the debug button. Make sure that you select the correct function to debug.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/0*1SjOuUanC4aPnlgT" /></figure><p>In the screenshot below you can see the debugger in action. The function started and stopped at the “debugger” keyword on line 6.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NgjBHdloF2V0FtkW" /></figure><p>To resume the execution of the code I clicked on the play button. This action will resume the function until it encounters a debugger statement or a breakpoint. In this case it encountered a breakpoint on line 14 and stopped. The three buttons next to the play button provide finer grained control over the debugger. When you hover over the buttons they will show some information on what they do.</p><p>On line 14 I placed a breakpoint. Breakpoints can be placed by clicking left of the line number where you would like the debugger to stop. Notice the variables section and the localNumber variable pointed out by the green arrow. The variable section will show you the values of all the variables.</p><p>The execution log pointed out by the yellow arrow contains the log information. This information will also be shown if the debugger is inactive.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/884/0*Xndifn5D3sMrkXCf" /></figure><h3>The call stack</h3><p>If you play a bit with the example code you will see that the debugger is straightforward and easy. I designed the example code to highlight an useful feature in the new debugger, the call stack.</p><p>The example code defines a global variable, and loops over two arrays. The outer loop walks through an array of four numbers and the inner loop walk through an array of 2 characters.</p><p>In the screenshot below I placed a breakpoint on row 19 and ran the debugger:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/929/0*NGj_VZ6ShzUqXF9Q" /></figure><p>In the call stack I selected the first line and the localCharacter variable shows as “A”. However when you select line 16 as shown in the next screenshot it becomes interesting.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/979/0*vkijIAET0s6MJvJO" /></figure><p>Now the local variable is localNumber and has the value 1. This is the value of localNumber when the inner loop over the two characters started. This way you can see all the variables that are relevant to the current state of the code.</p><h3>Logging</h3><p>Logging is the tool that will enable you to gather information about the code while it is running. Consider the following code:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1de214c055538654466546b168ec5c7f/href">https://medium.com/media/1de214c055538654466546b168ec5c7f/href</a></iframe><p>Nothing special here, but when we run it we see the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/478/1*jgZFdI5dIU-_Pd_dtvjqRg.png" /></figure><p>We now have two extra log levels, Error and Warning. These levels are very useful to distinguish between the severity of the message.</p><h3>Stackdriver logging</h3><p>Logging can be taken to the next level if you use the stackdriver logging. This is just a teaser, using stackdriver does require a deeper understanding of the Google Cloud Platform and the project that is linked to the script. I will only show some high level information here.</p><p>The screenshot below shows the log browser on the Google Cloud Platform:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MH8eLxuMqQdN87h2" /></figure><p>Besides a great way to explore the logs it is also possible to direct the logs of multiple scripts to a single location. Another great feature are the alerts, based on metrics you can trigger an email or other alerts to notify a stakeholder when something is wrong with the application.</p><h3>Timing</h3><p>Last but not least, timing. Once you debugged the code, and logging provides runtime information you might need performance metrics.</p><p>In the following code we can measure the time a section of code takes to execute.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/71683a4a53140364b83f859435a0c628/href">https://medium.com/media/71683a4a53140364b83f859435a0c628/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/503/0*O7n_6QJP6rpEeLzj" /></figure><p>Simple, yet powerful. This piece of code took 52 milliseconds to run.</p><h3>Wrapping up</h3><p>In this article I have shown how to use the debugger, log information and measure time. These features will provide you with information about the inner workings of your code. The best way to do this is, is to play with the examples and implement the logging statements.</p><p>Stackdriver logging is useful in a professional environment where reliability becomes more important. It takes a bit more time to set up, however it is worth it when you get an email with the error before you get a phone call from the user of your script.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8996b6a06cc6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-developer-experts/how-to-get-the-most-out-of-the-new-google-apps-script-debugger-logging-8996b6a06cc6">How to get the most out of the new Google Apps Script debugger &amp; logging</a> was originally published in <a href="https://medium.com/google-developer-experts">Google Developer Experts</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Structure and simplify your Google Apps Script (with) V8]]></title>
            <link>https://medium.com/swlh/structure-and-simplify-your-google-apps-script-with-v8-37e29d3e1edf?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/37e29d3e1edf</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Wed, 20 May 2020 20:07:06 GMT</pubDate>
            <atom:updated>2020-05-22T12:34:33.903Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_uuP2_nW40O2kLxH" /><figcaption>Photo by <a href="https://unsplash.com/@karsten_wuerth?utm_source=medium&amp;utm_medium=referral">Karsten Würth</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Apps Script now supports the <a href="https://developers.google.com/apps-script/guides/v8-runtime">V8 runtime</a>. Time for an update to the story “<a href="https://medium.com/javascript-in-plain-english/create-javascript-objects-in-google-apps-script-91472378ab55">Structure and simplify your Google Apps Script Apps script</a>”. The V8 runtime brings modern <a href="https://en.wikipedia.org/wiki/ECMAScript">ECMAScript</a> syntax to Apps Script. So we can now use arrow functions and destructuring assignments. No more <a href="https://medium.com/javascript-in-plain-english/https-medium-com-javascript-in-plain-english-what-is-hoisting-in-javascript-a63c1b2267a1">hoisting</a> with let and const and my personal favorite, template literals.</p><p>In this story I will show the added value of the changes I made in the constructor pattern using the factory functions. I will also show how to implement chaining and a builder pattern in Apps Script.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3ab40a8d5821e1edb643d96dd67aa20b/href">https://medium.com/media/3ab40a8d5821e1edb643d96dd67aa20b/href</a></iframe><p>The function now uses arrow functions for all functions and variables. All variables are now const unless they actually change. This solves one issue that I ran into several times. A function executes and the result is unexpected. This will happen in the code below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/787/1*ylpBVlQl_n4-7-LmtqQ64g.png" /></figure><p>This code can be saved and will execute in Apps Script V8. However, the following code won’t save</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*gVVa7SA2zFlwLe5H7oaARA.png" /></figure><p>So what if we put the code in multiple files:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*DJSYNoojn6MEeB1IjpyKcg.png" /></figure><p>The code will save, but won’t execute. So just changing the way we write the functions will protect us from naming conflicts. There is a an <a href="https://issuetracker.google.com/issues/157080190">issue</a> when using this method for the main function in libraries, so please star <a href="https://issuetracker.google.com/issues/157080190">issue</a> and so Google will make it an priority.</p><p>The other item I would like to highlight is the logManager. The assignment to the variable now uses the standard console logger, or if specified a manager. In my case this is an object build by a factory function that also writes some log information to a Google sheet.</p><blockquote>And, the usage of template literals logManager.log(`Created ${objectName}`); literal just makes the code a lot nicer.</blockquote><p>The factory pattern also makes it easy to implement two other interesting concepts, method chaining and the builder pattern. These concepts are not new to V8, they were already possible in earlier versions. With V8 there are more options, still the factory functions eleminate the need for <strong><em>this</em></strong> or classes altogether.</p><p>Consider this example:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/25ea9cd1c3e6a4b1fff62c8095cb17b1/href">https://medium.com/media/25ea9cd1c3e6a4b1fff62c8095cb17b1/href</a></iframe><p>By simple assigning the current object to me and return me with the fucntion run, we can chain the function run as: appObjectV8({}).run().run();</p><p>Or implent the builder pattern to use the following construction appObjectV8({}).prepare().run();</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4febf633c4184636dfd620161ce42172/href">https://medium.com/media/4febf633c4184636dfd620161ce42172/href</a></iframe><p>In this story I have shown what is now possible in V8. Using const to declare your functions will make your code more resilient. Method chaining and builder patterns are easy to implement in factory functions.</p><p>A full working example of the old implementation can be found in my article <a href="https://medium.com/@duizendstra/record-time-and-activities-with-google-sheets-calendar-and-apps-script-41bf69244346">Record time and activities with Google Sheets, Calendar and Apps Script</a>. This solution is also available in the <a href="https://developers.google.com/gsuite/solutions/calendar-timesheet">G Suite Solutions Gallery</a>. The code for this solution will be updated soon.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=37e29d3e1edf" width="1" height="1" alt=""><hr><p><a href="https://medium.com/swlh/structure-and-simplify-your-google-apps-script-with-v8-37e29d3e1edf">Structure and simplify your Google Apps Script (with) V8</a> was originally published in <a href="https://medium.com/swlh">The Startup</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Create JavaScript objects in Google Apps Script]]></title>
            <link>https://javascript.plainenglish.io/create-javascript-objects-in-google-apps-script-91472378ab55?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/91472378ab55</guid>
            <category><![CDATA[douglas-crockford]]></category>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[javascript-tips]]></category>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Sun, 15 Sep 2019 10:40:53 GMT</pubDate>
            <atom:updated>2019-09-24T08:20:06.915Z</atom:updated>
            <content:encoded><![CDATA[<h3>Structure and simplify your Google Apps Script</h3><h4>Organise your JavaScript objects in Google Apps Script with the constructor pattern by Douglas Crockford.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pEmH_I8cAFScwno_" /><figcaption>Photo by <a href="https://unsplash.com/@evphotocinema?utm_source=medium&amp;utm_medium=referral">Evan Dvorkin</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Factory functions and Apps Script are a good match. Structuring your code in App Script with these factory functions simplifies the process of developing and maintaining code. In this article I provide examples and background information on why I use the pattern.</p><h3>The background</h3><p>Apps Script is based on JavaScript 1.6, plus a few features from 1.7 and 1.8. It is synchronous as long as you stay on the server side. Editing is done in a limited online editor. We are waiting for an update of the JavaScript engine and an updated editor. External additions have been build to overcome some of the limitations, for example <a href="https://developers.google.com/apps-script/guides/clasp">clasp</a> to work with a local editor.</p><p>I am looking forward to the next big iteration of Apps Script, however the main reason I love to solve problems with Google Apps Script is it’s simplicity. Everything you need is there once you opened the Apps Script editor. You can choose your own abstraction layer, use the <a href="https://developers.google.com/apps-script/reference/drive/drive-app">Class DriveApp</a> to quickly create a file. Move on to the <a href="https://developers.google.com/apps-script/reference/drive/">Drive Service </a>if you need to do something fancy and use <a href="https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app">UrlFetchApp</a> if you need something specific from Drive V3. Synchronous code forces you to rethink your solution and avoids the complexity of callbacks or promises.</p><p>Simplicity and going back to the basics aligns well with Douglas Crockford’s <a href="https://www.amazon.com/JavaScript-Good-Parts-Douglas-Crockford/dp/0596517742">Javascript the Good Parts</a>. Yes, there is a debate if this book is still relevant. If you want to know more, my suggestion is to skip the debate. First understand that it is about understanding the important parts of the JavaScript language. Then read <a href="https://github.com/dwyl/Javascript-the-Good-Parts-notes">Notes on Douglas Crockford’s Javascript the Good Parts </a>. Then relax and watch some youtube. The video below starts at the point of creating objects but the whole video is still worth watching.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fbo36MrBfTk4%3Fstart%3D2144%26feature%3Doembed%26start%3D2144&amp;url=http%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dbo36MrBfTk4&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2Fbo36MrBfTk4%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7667069390211d2c744fc54d4e192254/href">https://medium.com/media/7667069390211d2c744fc54d4e192254/href</a></iframe><h3>Using this in Apps Script</h3><p><em>No pun intended with </em><em>this. I don’t use </em><em>this in JavaScript at all. If you use the factory pattern to create an object, </em><em>this behaves as it normally would, it is one of the benefits.</em></p><p>So now back to Apps Script and a method of structuring your code. This is the template I use for my Apps Script code.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1d0eab4510c6485bf5034c65867a964a/href">https://medium.com/media/1d0eab4510c6485bf5034c65867a964a/href</a></iframe><p>And to call the code, in this case passing the id of the active spreadsheet.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d06dc1573a880908a8934ea01591aa8a/href">https://medium.com/media/d06dc1573a880908a8934ea01591aa8a/href</a></iframe><p>The generic advantages of using factory functions are discussed in this article <a href="https://web.archive.org/web/20151029152324/http://ericleads.com/2013/01/javascript-constructor-functions-vs-factory-functions">JavaScript Constructor Functions Vs Factory Functions.</a></p><p>The factory functions can be organised into different files in the apps script editor. Each file contains a single function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/585/1*DkmnMd3gfbz9PEK-qtwWQw.jpeg" /></figure><p>And use another file to execute the code</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*eHZINUvS1ta9WrCdLZ_d3A.png" /></figure><p>This approach also works nicely together with <a href="https://developers.google.com/apps-script/guides/libraries">libraries</a>. In the example below I moved the code into a library and included that with the name “resources”. The typeahead works well, and the internals of the object are hidden and protected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*tXl_aZ7RnxgmV4NHQzmMlQ.png" /></figure><p>For larger projects you can organise the factory functions in a repository and use <a href="https://developers.google.com/apps-script/guides/clasp">clasp</a>.</p><h3>Conclusions</h3><p>Some of the conclusions are drawn in the linked articles. This article focusses on the added value of using this pattern in Apps Script. I like the way the old style JavaScript fits in the structure of Apps Script. The factory functions can be neatly organised in the files in the editor. Once written you can forget about the internals of the function and call it when needed. This makes the code more abstract where it needs to be abstract, where you use the object.</p><p>You can find a full working example in my article <a href="https://medium.com/@duizendstra/record-time-and-activities-with-google-sheets-calendar-and-apps-script-41bf69244346">Record time and activities with Google Sheets, Calendar and Apps Script</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=91472378ab55" width="1" height="1" alt=""><hr><p><a href="https://javascript.plainenglish.io/create-javascript-objects-in-google-apps-script-91472378ab55">Create JavaScript objects in Google Apps Script</a> was originally published in <a href="https://javascript.plainenglish.io/">JavaScript in Plain English</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to easily obtain a Google ID token]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://duizendstra.medium.com/how-to-easily-obtain-a-google-id-token-f1cde61541f0?source=rss-b87cf4ebf968------2"><img src="https://cdn-images-1.medium.com/max/2600/1*mgSbmEavmaEyiy13golkmw.jpeg" width="3100"></a></p><p class="medium-feed-snippet">Obtain a Google ID token for authentication with the Extensible Service Proxy</p><p class="medium-feed-link"><a href="https://duizendstra.medium.com/how-to-easily-obtain-a-google-id-token-f1cde61541f0?source=rss-b87cf4ebf968------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://duizendstra.medium.com/how-to-easily-obtain-a-google-id-token-f1cde61541f0?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/f1cde61541f0</guid>
            <category><![CDATA[oauth]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Sat, 07 Sep 2019 11:29:29 GMT</pubDate>
            <atom:updated>2019-09-07T11:29:29.363Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Solving the problem of migrating Google Drive files]]></title>
            <link>https://medium.com/google-workspace/solving-the-problem-of-migrating-google-drive-files-809316b70b37?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/809316b70b37</guid>
            <category><![CDATA[google-drive]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Thu, 27 Jun 2019 18:05:09 GMT</pubDate>
            <atom:updated>2019-06-27T19:49:39.300Z</atom:updated>
            <content:encoded><![CDATA[<p>I have seen many scenarios where we moved Google Drive files into a G Suite environment. In one scenario the files belonged to <a href="https://support.google.com/accounts/answer/181526?hl=en">conflicting accounts</a>. In another case a we moved a set of user accounts and related content to another G Suite environment.</p><p>Google Drive files can be migrated using the standard tooling. Or you can have the owners migrate the files themselves. This copy is a new document and bookmarks or other references to this document are lost. The shares on the document can be recreated and the <a href="https://gsuiteupdates.googleblog.com/2017/11/copy-comments-and-suggestions-in-docs.html">comments can also be copied</a>. You will lose the revision history.</p><p>With the latest G Suite to G Suite migration I stumbled upon a nice alternative. The alternative enabled me to move Google Drive files without unwanted changes. The only requirement was The G Suite environment should have shared drives enabled. Shared drives, <a href="https://gsuiteupdates.googleblog.com/2019/04/shared-drives.html">formerly known as team drives</a>.</p><h4>Proof of concept</h4><p>Create a shared drive in the environment that should receive the documents. Share this shared drive with the account that owns the documents. Make sure the account is a manager of the shared drive. Open the shared drive with the account that is owner of the files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/988/1*hSKrm2KtQzYWItB8xHqGEQ.png" /><figcaption>The shared drive</figcaption></figure><p>Select a file owned by the account and move it into the shared drive. You will get this message, and this is exactly what we want. The ownership will change to the shared drive.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/605/1*uGcoTe8AsGS6XCvH-VKkWg.png" /><figcaption>Confirmation of the ownership change</figcaption></figure><p>Open the shared drive with the account in the G suite environment that should get the ownership. Select the file and move the file out of the shared drive in the My Drive.</p><p>The message is different now. Again it states what we want, the new ownership.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/1*I0v7p-bd21M5f-pYubAiRg.png" /></figure><p>Check the ownership of the file and you will see that it is now owned by the account in the G Suite environment.</p><p>This works in all directions. You can move files between G Suite environments and from consumer to G Suite and back. The only requirement is that the G Suite environment has shared drives.</p><h4>This is relevant because?</h4><p>In every migration you have many choices. This trick adds the option for users to keep the existing drive documents as is. Links will keep working. The revision history is still there and no need to explain that a copy of the document should is the one to use now .</p><p>It also adds the choice of automation or self service. You can give users a shared drive and present it as a moving box. They can move the files in the box and move them out in the new environment.</p><p>Another option is to automate the whole process. Record the current state of the files and move them into the new domain using the API’s. Then rebuild the structure in the My Drive. This process takes time to build and run. The advantage is that the end users files are migrated without much effort from their side.</p><p>The last option is to embrace shared drives. Use the migration to restructure existing My Drive file structures. Use a combination of automation and end user effort. Then move files into the shared drives in the target environment.</p><p>There are some details to keep in mind. Moving folders requires an extra role for each user. Permissions on folders are not retained when moved in and out of team drives. Make sure you test this as an end user, not as a super admin. Read the support document <a href="https://support.google.com/a/answer/7374057">Migrate content to a shared drive</a>.</p><p>Using shared drives can help to perform a tough part of a G Suite migration, the Google Drive migration.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=809316b70b37" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-workspace/solving-the-problem-of-migrating-google-drive-files-809316b70b37">Solving the problem of migrating Google Drive files</a> was originally published in <a href="https://medium.com/google-workspace">Google Workspace</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Record time and activities with Google Sheets, Calendar and Apps Script]]></title>
            <link>https://duizendstra.medium.com/record-time-and-activities-with-google-sheets-calendar-and-apps-script-41bf69244346?source=rss-b87cf4ebf968------2</link>
            <guid isPermaLink="false">https://medium.com/p/41bf69244346</guid>
            <category><![CDATA[google-apps-script]]></category>
            <category><![CDATA[calendar]]></category>
            <category><![CDATA[g-suite]]></category>
            <category><![CDATA[productivity]]></category>
            <dc:creator><![CDATA[Jasper Duizendstra]]></dc:creator>
            <pubDate>Wed, 19 Jun 2019 19:30:58 GMT</pubDate>
            <atom:updated>2019-06-19T19:30:58.513Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QlVyAX1doom6llJzmlSrOQ.png" /><figcaption>Organizing</figcaption></figure><p>Keeping a record of time spent on tasks can be a challenge. Should you update daily, weekly, make notes, use a spreadsheet or just a piece of paper? The dreaded monthly email, please enter your time, it is the end of the month and we need to send the invoices. I have been in situations where I needed to enter the time in three systems, one of them being an terminal based program.</p><p>Keeping the records in sync, find a frictionless way to record daily time became important to me. The first iteration of getting control over the process was maintaining my own spreadsheet, in my own format, and at the end of the week just copy paste the data into all the systems. Some optimization was possible, once the data was organised in the sheet I could order it in a way to make the copy paste action easier.</p><p>There was still lots of room for further optimization, mainly by entering the time easier and defer some of the bulk work to a later point in time. Google Calendar is great to use as the place to input time. Create a secondary calendar and start recording time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*Lk5S1jX6Fi50ostjFWF9kg.png" /><figcaption>Keeping track of the day</figcaption></figure><p>Most of the interface ito write time is already there. Just add a title that is enough to remember what the record is about. Then at the end of the week, open a google sheet and with a single action all entries are imported into the time spreadsheet. The records can use some structuring so we can report on project, customer and task.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BiT8tiJmtLLMJC3D8tdfIg.png" /><figcaption>The calendar entries organised in the sheet</figcaption></figure><p>Once the customer, project and tasks are selected the description can be copied and maybe cleaned up a bit. At this point the time records are structured and can be used in other processes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1mpcTyi7gg14R73oIb08XA.png" /></figure><p>The QUERY function is a great way to build the dashboard. Or make a quick and dirty overview of the hours spend on Project X.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NAd71uMG9MebWMvtKOw0mA.png" /></figure><h3>Interested? Here is how to set up the sheet.</h3><p>Disclaimer, some technical knowledge is required to set the sheet up. The code is open and there are examples in the sheet. If you get stuck feel free to ask me for help in the comments.</p><p><strong>The setup part one</strong>, first create your secondary calendar where you will enter the time. There is a plus sign just below “other calendars”. Then select the “Create new calendar” option</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/424/0*Rs77ZPpys6YGK6F7" /></figure><p>Give it a nice name and click on the blue button.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/0*nsvBzfu8NIYREcSr" /></figure><p>Last step for this process, copy the id of the calendar, we need it later</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5FWygQIBYee2uNuy" /></figure><p><strong>Part two</strong>, make a copy of the <a href="https://docs.google.com/spreadsheets/d/1ay_SBrFhIqbJs0-ifgU7E8jg4dmBbLlc4yIS_-PPSXA/copy">sheet</a>. In the configuration tab you should paste the id of the calendar you created in step one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/1*XLZobM_0fTUaK2GdWCeiDg.png" /></figure><p>At this point the sheet should work. This is a nice moment to enter some test entries in the calendar, maybe some projects on the code page and play a bit with the sheet to get a feeling on how it works. The menu option myTime will trigger the synchronisation. It will ask for an authorisation the first time. The code is open and explained in the rest of the article.</p><h3>How it works</h3><p>The menu option is added via the onOpen Action. The myTime object is constructed using the Crockford’s 2014 object creation pattern mentioned in this <a href="https://www.youtube.com/watch?v=PSGEjv3Tqo0">talk</a> and in JavaScript: The Good Parts.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/fd43f62f35457b93a97e66b2f69efac5/href">https://medium.com/media/fd43f62f35457b93a97e66b2f69efac5/href</a></iframe><p>The myTime function is kept simple. The usage of setFormulaR1C1 is important to enter the formulas in the sheet. The function uses the Higher-order functions some and forEach to handle the looping in an elegant way.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7905dc1bb86324863ff7fe3cd5fa0475/href">https://medium.com/media/7905dc1bb86324863ff7fe3cd5fa0475/href</a></iframe><h3>What’s next</h3><p>There is still room for optimization, think AI/ML to translate the description into the customer, project and task. Or simply implement a basic #tag system. Automate imports in others systems, or use the calendar to plan your time and then convert the planning into time written. There is more on the list, for now, as is, this sheet saves me time and provides me with a clear overview of time spend.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=41bf69244346" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>