AppsScriptPulse

Snippet, Beginner, Sheets

Move column positions in Google Sheets with Google Apps Script

Tags:
| |

Move columns in a Google Sheet

Move columns in a Google Sheet

The following Google Apps Script is designed to reposition 2 columns within a Google Sheet. It is a small and simple bit of code but it was something new to me when I was collating lots of files together and appending columns at the end – which then needed to be moved.

In this example I am moving columns ‘collegename’ (E) and ‘shoesize’ (F) to the left of ‘postcode’ (D). So that ‘postcode’ will be the final column (F).

Source: The Gift of Script: Move Column Positions

Solutions, Sheets, V8, Intermediate

Dependent dropdown lists for Google Sheets (and lots of other Google Apps Script goodies)

Tags:
| | 0 Comments |

Updated the script for dependent lists, and now it supports new options. New for 2022:

Dependent drop-down lists can be used to create a dynamic list of choices in Google Sheets. When the user makes a selection from the first drop-down list, the choices in the second drop-down list will be updated based on the selection. This can be used to create a cascading list of choices.

If you are looking for a nice solution to build dependent drop-down lists in Google Sheets this is a great solution from Max Makhrov. For more experienced Google Apps Script developers the solution incorporates some clever snippets both from Max and a number of other members of the community. Some highlights include: converting a column index into corresponding column letter; a tasker to batch apply updates to a Google Sheet; ChuckyCache for objects above 100Kb; and a reference to a zip compression solution. All these are referenced in the source code provided in the post.

Source: Dependent Drop-Down Lists For Google Sheets v2022

Solutions, Sheets, V8, Beginner

Quickly format multiple Google Sheets with Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script.

The idea of this post, came from the Department of Education in Hawaii, where they have over 300 schools and a guy who works there told me he had to format over 300 Google Sheets the same way, and wondered how this could be done with Apps Script.

Easy I said! And I thought it was a typical example of how a little knowledge of Apps Script can help you speed up your work and allow you to spend less time doing boring, repetitive work, and focus more on the data itself.

Let’s see how it’s done!

This is a nice real world example of how Google Apps Script can be used to make a manual routine task more efficient. The post by Baz Roberts includes a detailed explanation of what is going on in the code making it also a great learning resource. If you have some more complicated Google Sheets formatting in mind you might want to also look at Google Sheets Macros, which lets you record every action you take within the spreadsheet creating a script you could call similar to the format functions included in the post.

Source: Quickly format multiple Google Sheets with Apps Script (also available on bazroberts.com)

Add-on, Solutions, Sheets, Video Tutorial, Beginner

GWAOw! 3 – Crop Sheet by Eric Koleda (how to keep Google Workspace Add-on development simple with custom menus)

Tags:
| | 0 Comments |

Crop Google Sheet Data to the data range or a selection in 2 button click with Crop Sheet. Full Walkthrough

Does this Google Workspace Add-on developer story sound familiar? You are able quickly write the code that executes your add-on functionality, but then you spend days buried in HTML or Card Service making the UI. The Crop Sheet add-on by Eric Koleda highlighted in this latest GWAOw! episode is a great example of what is possible just by using custom menus. For what it is worth the source code (all 73 lines including comments!!!) is on GitHub. Follow the source link for the video demo from Scott Donald and links including the GitHub repo.

Source: GWAOw! 3 – Crop Sheet by Eric Koleda – Yagisanatode

Gotchas, Solutions, Sheets, Web App, V8, Intermediate

Benchmark: Process cost for HTML Template using Google Apps Script

Tags:
| | 2 Comments |

Image credit: Kanshi Tanaike

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.

Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:

Source: Benchmark: Process cost for HTML Template using Google Apps Script

Solutions, Sheets, V8, Video Tutorial, Beginner

How to access an API with Google Apps Script and API calls and Google Sheets manipulation

Tags:
| | 0 Comments |

In this video you will learn how to access an API with Google Apps Script.

Chanel Greco has created this video tutorial which walks through some different ways you can interact with other sites with Google Apps Script to get data into Google Sheets. For the tutorial Chanel uses the Weather API and by coincidence Luxman Ravindrakumar has also shared a similar tutorial on Medium explaining how to use the OpenWeather API. So if you prefer learning by watching or reading you’ve no excuses :). Check the sources links below for both tutorials.

Sources: How to access an API with Google Apps Script (YouTube) and API Calls & Spreadsheet Manipulation With Google Apps Script

Solutions, Drive, Sheets, V8, Intermediate

A tiny Google Apps Script tool to synchronize Google Drive permissions which can also process large amounts of data

Tags:
| | 1 Comment |

Image credit: Inclu Cat

Are you using Google Drive to share information with your staff? As the number of staff members increases, it can become increasingly difficult to manage permissions properly. I faced a similar issue. As the members of the team changed from time to time, setting access rights for each folder became a cost that could not be ignored. So I used Google Apps Script to create a tool to synchronize the permissions management table with the actual permissions.

We’ve previously featured Inclu Cat’s posts on ways to deal with Google Apps Script’s 6-minute execution limit [Ref 1 & Ref 2]. These posts continue to prove to be incredibly popular and usually feature in our most read list. Even if you aren’t interested in programmatically managing Google Drive file/folder permissions this post is a nice example of using the authors LongRun solution to process large amounts of data with Google Apps Script. From the source link you can find the GitHub repo with all the code you need and setup instructions.

Source: [Google Drive] A tiny tool to synchronize permissions

Solutions, Sheets, Intermediate, No Code

Discover how to interact with Coda webhook-based automations using Google Apps Script

Tags:
| | 0 Comments |

Discover how to interact with Coda webhook-based automations using Google Apps Script! In this post, we’ll explore different code patterns in both Coda’s formula language and Google Apps Script to send data contained in a Google Spreadsheet to a Coda table, and also perform other related tasks, using the new (May 2022) webhook-powered Coda automations.

For those unfamiliar with Coda:

Coda provides word-processing, spreadsheet, and database functions. It’s a canvas that blends spreadsheets, presentations, apps, and documents together. The software can integrate with third-party services like Slack and Gmail.

This post from Pablo Felip is a great opportunity to see what is possible using the new Coda webhook-powered automations. We’ve previously featured a couple of Coda/Apps Script integrations on Pulse, that have highlighted solutions using the Coda REST API and Coda Packs. This is the first we’ve seen using webhooks, which have a ‘no-code’ when/if/then setup interface. This route may provide the quick glue required for your Coda/Google Workspace integration. Pablo includes all the source code in the post as well as detailed instructions for setting up.

Source: Coda webhooks 💙 Google Apps Script!

Solutions, Sheets, V8, Beginner

Encouraging users to run setup scripts in Google Sheets container bound projects when they first make a copy

Tags:
| | 0 Comments |

I quite often get called upon by clients to create Google Sheet templates that have Google Apps Script Automations bound to them. Sometimes these Google Sheets require an automated setup process to run things like gathering initial data, setting up time triggers, approving scopes connected to onEdit() or onOpen() triggers or renaming connected forms and their contents.

One of the challenges is getting new owners of the duplicated template Google Sheet to run the bound script before they dive into working on the Google Sheet. … After quite a lot of trial and error, I have devised a pretty solid approach that seems to have the most success in getting users to run through the authorisation of scopes and then run the startup script.

Scott Donald shares his approach for helping users get setup when first using a container bound script project in Google Sheets. The process has some nice features including revealing additional sheets, changing the active sheet and cell as well as removing the setup sheet. The post also highlights some useful tips and considerations as part of the user experience including the type and placement of supporting information.

Scott has also shared a solution for removing a Google Sheets button after a script has run, which you might want to also check out if you are thinking of developing this process further.

Source: One Approach to Encourage Users to Run Google Sheet-Bound Apps Script When They First Make a Copy of the File – Yagisanatode

Snippet, Solutions, Forms, Sheets, Slides, Beginner

Create and run polls in Google Slides using Google Forms and Google Sheets

Tags:
| | 0 Comments |

Image credit: Prateek Sharma

I wanted to give a presentation to a group of people. In order to make the presentation more engaging & eliminate silences during the slideshow, I thought of conducting polls in between. … Since I am giving presentation using Google tools, I thought let’s try it out with Google Apps Script.

I thought this was a nice little Apps Script snippet from Prateek Sharma which hooks into a .onFormSubmit() trigger to update all charts embedded from Google Sheets in a Google Slides presentation. Prateek provides detailed steps including the code for setting this up. Unfortunately, a limitation of Google Slides is once you go into ‘slideshow’ mode all charts become static images so you need to jump back to the editor view to show real-time results.

Source: Create and Run Polls in Google Slides using Google Forms & Google Sheets