Google Apps Script Useful Snippets

A collection of small functions and/or classes to help along with development in GAS.

Function list:

Docs:

Count words in a Google Doc - countWords():

• Takes Google Doc File ID
• Returns the number of words in the Document

---

Drive:

Count all Files in Google Drive Folder - countFilesInFolder():

• Takes Google Drive folder ID as parameter.
• Returns the number of items in the folder.

Permanently delete the files in a drive folder (non recursive) using batch API - deleteFiles():

^{Committed by: Martí Angelats i Ribera.}

• Takes Google Drive Folder Object as parameter
• Takes optional separate parameter file => file.getName() === 'File to remove' removing all files named "File to remove" from the root folder.
• Returns undefined

---

Sheets:

Count number of Formulae in Google Spreadsheet - getAllFormulae():

• Returns number of formulae in all sheets of a Spreadsheet file.

Convert Google Sheet to .xlsx Format - convertSheetToXlsx():

• Takes Spreadsheet ID and Drive folder ID as parameters
• Saves .xlsx version to user's Drive

Convert .xlsx File to Google Sheets Format - convertXlsxToSheet():

• Takes .xlsx File ID and Drive folder ID as parameters
• Saves converted Google Sheet to user's Drive

Get Sheet by GID - getSheetById()

• Takes a gid as a string
• Returns the Sheet object of the sheet in the current Spreadsheet with the given gid

---

Sheets (Custom Functions):

Filter a range of values out of another range of values - filterValues()

• Takes two column ranges (eg A1:A1 and B1:B)
• Returns column range containing values from range 1 with values from range 2 removed
• Context: https://stackoverflow.com/a/63848446/11551468

---

UrlFetchApp:

Obtain Final Location of given URL - getLocation():

• Returns end location of a URL if passed through shortener.

Turn JSON object of URL parameter-value pairs into URL parameter string - urlParameterfy()

• Takes JSON object of parameters and their values
• Returns single string of all URL parameters ready for concatenation with URL