Get Qualtrics Survey details

The following Google Apps Script is designed to connect to the Qualtrics API for getting the details of a Survey with a given ID, such as its name and the questions.

Qualtrics API Survey details

Search Google Drive for Shared with me

The following Google Apps Script is designed to search Google Drive for all of the files found in the 'Shared with me' space. It will collate this information into a Google Sheet, including:

• The file name as a direct clickable link,
• The file ID,
• The type of file eg PDF, Google Sheet/Doc,
• The file creation date,
• The file last updated date,
• The folder path,
• The file owner.
  

Search Google Drive 'Shared with me' and collate the results

Bulk export Qualtrics survey results 2.0

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

This post is an updated version of the previous tool that has become redundant due to changes in the Qualtrics API.

Screenshot of instructional sheet requesting Qualtrics information

Autofill Google Sheet Formula

The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.

Autofill Google Sheet Formula

Move Column Positions

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).

Move columns in a Google Sheet

Remove File Access via a Web App

The following Google Apps Script is designed to remove a person's edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person's access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person's access.

This is achieved by some Apps Script code in the Google Sheet we want to remove access to (as this is a template file copied each time somebody completes a Google Form) that creates some hyperlink text to send the Sheet ID to the Web App when the user clicks it. The Web App itself extracts this ID, gets the person's email address from their connected session with the Web App and then executes as an admin account (or basically an account that retains access to the Google Sheet - as it will be filed away somewhere in Google Drive).

All of the complicated extras and fancy formatting has been stripped away from this solution so it focuses on the simple task of connecting with a Web App and removing access, to help make it clearer.

Web App code sample

Bulk convert Excel files to Google Sheet files - version 1.1

As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script is designed to iterate through a Google Drive folder of files and convert only the Microsoft Excel ones into individual Google Sheet files. The code is an upgrade of this original blog post to bulk convert Excel files to Google Sheet files.

The Code

We use the method 'getMimeType()' in order to be able to iterate through all of the files and distinguish between them, so we do not try to convert anything other than Microsoft Excel, which would likely result in an error.

Get all selected Ranges in a Google Sheet

The following Google Apps Script was from some learning around multiple ranges being selected/highlighted by a user in a Google Sheet. I have always worked with individual cells or continuous ranges within Sheets, but during the development of my Archive Sheet data Add-on I wanted to enhance it by allowing more rows to be acted upon in one go.

I practiced getting a few values after looping through each selected range:

• A1 Notation (e.g. A2:C5),
• Cell values,
• Starting row,
• End row,
• Number of rows (calculated from the above),
• Starting column,
• End column,
• Number of columns (calculated from the above).

Selected ranges in a Google Sheet