Move/archive a row of data from one Google Sheet (tab) to another, within the same file, using this Google Workspace Add-on. It will also remember your individual preferences for each file so it is even quicker to use next time around.
New Features January 2022
A new ‘Settings’ page allowing you to select more than just cell values when archiving row data. You can now also select:
> Text formatting: bold, italics, colour, size and style.
> Cell: background colour, Notes and wrapping.
How to change Google Drive file permissions from Editor to Viewer as said user.
The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.
Create a new Sheet tab within an existing Google Sheet file and format a header row (font size, colour, alignment, etc) for appending data to.
Header row
The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:
Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
Reduce the overall number of columns within the Sheet.
Insert 1 row of data to use as the Header row.
Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
Set the Header row background colour, height and column widths.
Convert a Google Doc into a PDF in a given folder and optionally delete the original Doc file.
The following Google Apps Script is designed to create a PDF file of a Google Doc in a Drive folder that you specify, with the option to delete the original Doc. This snippet of code is from larger developed solutions and allows you to understand and replicate the process.
This post is somewhat of an updated version of this Convert Doc to PDF and move into a new folder blog post. Whilst the code in it still works it was part of my early days of learning Apps Script and it was also when Google allowed for files to exist in more than one place (a feature now replaced by Shortcuts).
Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
Continue from where it left off – if you have a lot of Google folders and files to handle and the above runtime is reached the tool will complete the current row/folder and prompt you to run it again, without duplicating any folders or files already created.
Popups – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
Full naming control – you have the ability to tweak every single folder and file name that is created by the tool. See the Concatenate section below to understand the true power of this.
Clickable links – created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
Add more files – the tool currently has columns for 10 files to be added to each folder, but you can actually append more as long as you follow the same convention of 2 columns per file (1 file name and 1 file ID). Do this before you run the tool as you cannot add more files to a folder once it has been created.
Add more folders – after running the tool you can either choose to clear all of the data within it (use the ‘Reset sheets’ option from the ‘Admin’ menu) and start again or you can simply append further rows/folders and re-run without any duplication (if the ‘Folder Link’ column is not blank/empty then it will skip that row).
Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterwards. Specify the destination folder for the PDFs.
Bulk convert Google Docs to PDFs using Apps Script
The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.
Bulk create Google Drive folders with optional files copied into each one. Also optionally add edit permissions to these folders/files.
Bulk create Google Drive folders and add files, from a Sheet of data
The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet. There is also the option to add specific ‘edit’ permissions to the newly create Drive folders of which the files would automatically inherit this access level.
Key Functionality:
Complete the necessary information in the Config sheet before proceeding. Then use the Create folders option from the Admin menu at the top of the Google Sheet.
Adding permissions is optional – use the Config sheet to change the dropdown as required. If you select ‘No’ then the usual Google Drive inheritance will occur based on the parent Google Drive folder.
You can add multiple File IDs into the relevant cell and they can be different for each row. Leaving this cell blank/empty means no files will attempt to be copied into the new folder.
The original filename will be updated during the copy to append the folder name to the end of it, in order to prevent creating a large number of files with identical names.
There is a Log sheet to help troubleshoot any errors which may occur when running the tool.
Use Google Apps Script to create a prefilled URL of data that can be submitted to a Google Form.
Screenshot of Google Form questions
The following Google Apps Script is designed to submit specific data to a Google Form, by creating a prefilled URL. The reason for this was I needed some way of sending data from a number of individual Google Sheets (that I did not own) to one central location, but crucially it needed to be anonymised.
Sending the data directly to a Google Sheet includes version history both in the file and cell meaning it was not truly anonymous. Whereas sending the data through a Google Form and then on to the Response Sheet did strip away anything identifiable.
The logic behind this code is to create a prefilled URL which contains answers to each of the Forms questions. Use the previous webpage link to learn how to create such a URL first so that you understand each question on a Form has a unique value.
Compare a number of email addresses and check their domains against one you are filtering for via a regular expression.
Screenshot of email addresses in a Google Sheet cell
The following Google Apps Script is designed to use a regular expression (regex) to confirm if an email address domain matches one we specify eg: @hotmail.co.uk, @gmail.com, @outlook.com
I developed the code so I could screen email addresses to make sure they only came from within the institution – otherwise it would have broken the tool I was developing for creating Zoom meetings. The email addresses in this example are all stored in one cell in a Google Sheet and are separated via a comma and a single space.
