Create and delete a trigger via "toggling", works well with buttons.
function myFunction() {
Logger.log(`Test`)
}
function onButtonPress() {
const scriptProperties = PropertiesService.getScriptProperties().getProperties()
if (scriptProperties.hasOwnProperty(`MyTrigger`)) {
const triggerID = scriptProperties.MyTrigger
toggleTrigger(triggerID)
PropertiesService.getScriptProperties().deleteProperty(`MyTrigger`)
} else toggleTrigger()
}
function toggleTrigger(id = false) {
if (!id) {
ScriptApp.newTrigger(`myFunction`)
//
.timeBased()
.everyMinutes(10)
//
.create()
const myTriggerID = ScriptApp.getScriptTriggers().find(trigger => trigger.getHandlerFunction() === `myFunction`).getUniqueId()
PropertiesService.getScriptProperties().setProperty(`MyTrigger`, myTriggerID)
} else ScriptApp.deleteTrigger(ScriptApp.getScriptTriggers().find(trigger => trigger.getUniqueId() === id))
}
Extract and remove data from primary to one or more arrays.
function FilterIntoArrays() {
let data = [`a`, 1, `b`, `c`, 2, `d`, `e`, 3]
// Declare new array(s) to filter into.
const numbers = []
data = data.filter(cell => {
// Apply logic for filtering.
if (isNaN(cell)) {
// Keep in primary array.
return true
} else {
// Push to array, remove from primary.
numbers.push(cell)
return false
}
})
// data: [a, b, c, d, e]
// numbers: [1.0, 2.0, 3.0]
}
Return the last row of specified index.
function LastRowOfColumn(data, TARGET_INDEX = 0) {
// Assuming no blank cells up to end row.
// Always add the difference between starting row of data and Row 1.
return data.flatMap(i => i[TARGET_INDEX])
.filter(i !== ``)
.length
}
Remove duplicate rows by specified index.
function RemoveDuplicates(my2DArray, TARGET_INDEX) {
let data = my2DArray;
[...new Set(data.flatMap(i => i[TARGET_INDEX]))]
.map(i => data.filter(item => item[1] === i))
.filter(i => i.length > 1)
/* To remove 'last appearing': */
// .map(i => i[i.length-1])
/* To remove 'first appearing': */
// .map(i => i[0])
.forEach(i => {
data.splice(data.findIndex(item => item === i), 1)
})
/* Reinsertion: */
// MY_SHEET.getRange(START_ROW, START_COLUMN, data.length, data[0].length).setValues(data)
/* Or, return: */
// return data
}
onEdit() only in specified range.
function onEdit(e) {
// MY_SHEET A2:A1000
if (e.source.getActiveSheet().getName() === `MY_SHEET`) {
if (
(e.range.columnStart >= 1 && e.range.columnEnd <= 1) &&
(e.range.rowStart >= 2 && e.range.rowEnd <= 1000)
) { Logger.log(e.range.getValues()) }
}
}
Rows to Columns, or Columns to Rows.
/* [`A1`, `A2`, `A3`] [`A1`, `B1`, `C1`]
[`B1`, `B2`, `B3`] <=> [`A2`, `B2`, `C2`]
[`C1`, `C2`, `C3`] [`A3`, `B3`, `C3`] */
function TransposeData(data) {
return data[0].map((row, index) => data.flatMap(row => row[index]))
}
Backup selected Drive files.
You must have permission to access file(s).
You must set a folder to store backups.
Triggering 'RUN' will create backup copies of documents or files listed in 'SOURCES'.
The copies are automatically stored in self-titled folders, and are prefixed with a [dd/mm/yy] timestamp.
All Workspace documents retain attached script at time of backup, and all triggers are removed.
Code.gs
// Target save folder ID.
const PRESERVE_FOLDER_ID = `...`
function RUN() {
SOURCES.forEach(src => {
try { DriveApp.getFileById(src.ID) }
catch(err) { return Logger.log(`=> [${src.Title}] : (ERROR) Bad file ID or not authorized.`) }
return HANDLE.File({ ...src, FolderID: HANDLE.Folder(src) })
})
}
Sources.gs
const SOURCES = [
{
Title: `My Sheet`,
ID: `...`
},
{
Title: `My Doc`,
ID: `...`
}
]
Handlers.gs
const TIMESTAMP = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), `dd/MM/yy`)
const HANDLE = {
Folder: (src) => {
const PRESERVE_FOLDER = DriveApp.getFolderById(PRESERVE_FOLDER_ID)
const folders = PRESERVE_FOLDER.getFolders()
while (folders.hasNext()) {
const folder = folders.next()
if (folder.getName() === src.Title) return folder.getId()
}
return PRESERVE_FOLDER.createFolder(src.Title)
.getId()
},
File: (src) => {
const copyID = DriveApp.getFileById(src.ID)
.makeCopy()
.moveTo(DriveApp.getFolderById(src.FolderID))
.setName(`[${TIMESTAMP}] ${src.Title}`)
.getId()
Logger.log(`=> [${src.Title}] : ${copyID}`)
},
}
"tHiS IS ProPer case" || [`tHiS`, `IS`, `ProPer`, `case`] => "This Is Proper Case"
function ToProperCase(data) {
if (Array.isArray(data)) {
return data.map(item => {
return (item.split(` `).length)
? item.split(` `)
.map(word => `${word.charAt(0).toUpperCase()}${(word.length > 1) ? word.slice(1).toLowerCase() : ``}`)
.join(` `) // <-- Remove if you would like to preserve array.
: `${item.charAt(0).toUpperCase()}${(item.length > 1) ? item.slice(1).toLowerCase() : ``}`
})
} else {
return (data.split(` `).length)
? data.split(` `)
.map(word => `${word.charAt(0).toUpperCase()}${(word.length > 1) ? word.slice(1).toLowerCase() : ``}`)
.join(` `)
: `${data.charAt(0).toUpperCase()}${(data.length > 1) ? data.slice(1).toLowerCase() : ``}`
}
}
A handful of date-oriented custom functions.
/** Get preferred format of date of your choosing */
function FormatDate(date) {
if (typeof date === `string`) { date = new Date(date) }
return Utilities.formatDate(date, `America/New_York`, `MMM-dd yyyy`)
}
/** Get number of days since date */
function DaysSince(date) {
if (typeof date === `string`) { date = new Date(date) }
return Math.floor((new Date().getTime() - date.getTime()) / (1000 * 60 * 60 * 24))
}
/** Get number of days between two dates */
function DaysBetween(startDate, endDate) {
if (typeof startDate === `string`) { startDate = new Date(startDate) }
if (typeof endDate === `string`) { endDate = new Date(endDate) }
return Math.floor((new Date(endDate).getTime() - new Date(startDate).getTime()) / (1000 * 60 * 60 * 24))
}
/** Get the date from X days ago */
function DateFromDaysAgo(number) {
return new Date(new Date().getTime() - (number * (1000 * 60 * 60 * 24)))
}
/** Get an array of dates from X days ago */
function DatesFromDaysAgo(number) {
return [...Array(number)].map((_, index) =>
new Date(new Date().getTime() - ((index+1) * (1000 * 60 * 60 * 24)))
)
}
/** Get an array of dates between two dates */
function DatesBetweenDates(startDate, endDate) {
if (typeof startDate === `string`) { startDate = new Date(startDate) }
if (typeof endDate === `string`) { endDate = new Date(endDate) }
const length = Math.floor((new Date(endDate).getTime() - new Date(startDate).getTime()) / (1000 * 60 * 60 * 24))+1
return [...Array(length)].map((_, index) => new Date(endDate.getTime() - ((index) * (1000 * 60 * 60 * 24)))
)
}
Apply a custom sort order and preserve styles.
/* Usage */
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`MY_SHEET`)
const range = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn())
const sortBy = [`Bronze`, `Silver`, `Gold`, `Platinum`]
CustomSort(range, 2, sortBy)
/* End Usage */
function CustomSort(range, columnNumber, sortOrderArray) {
// Convert array to object for numeric keys. ( 0: `Item1`, 1: `Item2`, ...)
const sortOrder = { ...sortOrderArray }
// Store initial values and styles.
const INIT = {
Values: range.getValues(),
Formulas: range.getFormulas(),
Bandings: range.getBandings(),
FontStyles: range.getFontStyles(),
// Removes white backgrounds to preserve bandings.
BackgroundColors: range.getBackgrounds().map(row => row.map(cell => (cell === `#ffffff`) ? `` : cell))
}
// Convert each row into an object and assign relevant properties, then sort.
const DATA = INIT.Values.map((i, index) =>
i = {
RowData: i,
SortIndex: parseInt(Object.keys(sortOrder).find(key => sortOrder[key] === i[columnNumber-1])),
Formula: INIT.Formulas[index],
FontStyle: INIT.FontStyles[index],
BackgroundColor: INIT.BackgroundColors[index]
}
).sort((a, b) => (a.SortIndex - b.SortIndex))
// Ready sorted data/styles for setting.
const SORTED = {
// Merges formulas with values, prevents overwriting non-formula cells as blank.
Values: DATA.map((row, rowIndex) => row.RowData.map((col, colIndex) => row.Formula[colIndex] || col)),
FontStyles: DATA.map(i => i.FontStyle),
BackgroundColors: DATA.map(i => i.BackgroundColor)
}
// Set data and styles.
range.setValues(SORTED.Values)
.setFontStyles(SORTED.FontStyles)
.setBackgroundColors(SORTED.BackgroundColors)
}
Add a dynamic menu to replace or insert sheets from a master spreadsheet.
/* GLOBAL */
const ACTIVE_SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet()
const MASTER_SPREADSHEET = SpreadsheetApp.openById(`/*MASTER_ID*/`)
const MASTER_SHEET_LIST = MASTER_SPREADSHEET.getSheets().map(i => i.getName())
for (let masterSheet of MASTER_SHEET_LIST) {
this[masterSheet] = () => {
// IF SHEET EXISTS IN ACTIVE SPREADSHEET:
if (ACTIVE_SPREADSHEET.getSheetByName(masterSheet)) {
const sheet = ACTIVE_SPREADSHEET.getSheetByName(masterSheet)
const sheetIndex = sheet.getIndex()
const activeSheetIndex = ACTIVE_SPREADSHEET.getActiveSheet().getIndex()
// AVOIDS ACTIVATING A HIDDEN SHEET DURING TRANSFER PROCESS
if (sheetIndex === activeSheetIndex) {
ACTIVE_SPREADSHEET.getSheets()
.filter(i => !i.isSheetHidden() && i.getIndex() !== sheetIndex)[0]
.activate()
} // Sorry if you have a huge sheet list.
// DELETE, COPY, RENAME, MOVE TO ORIGINAL INDEX
ACTIVE_SPREADSHEET
.deleteSheet(sheet)
MASTER_SPREADSHEET
.getSheetByName(masterSheet)
.copyTo(SpreadsheetApp.getActiveSpreadsheet())
.setName(masterSheet)
.activate()
ACTIVE_SPREADSHEET
.moveActiveSheet(sheetIndex)
// IF SHEET DOESN'T EXIST IN ACTIVE SPREADSHEET:
} else {
// COPY, RENAME
MASTER_SPREADSHEET
.getSheetByName(masterSheet)
.copyTo(SpreadsheetApp.getActiveSpreadsheet())
.setName(masterSheet)
.activate()
}
}
}
/* END GLOBAL */
/* Usage (onOpen) */
function ADD_MENU() {
const UI = SpreadsheetApp.getUi()
const MENU = UI.createMenu(`BETA`)
MASTER_SHEET_LIST.forEach(i => MENU.addItem(i, i))
return MENU.addToUi();
}
/* End Usage */