Skip to content

Instantly share code, notes, and snippets.

Avatar
🎯
Focusing

pfelipm

🎯
Focusing
View GitHub Profile
@pfelipm
pfelipm / Bookmarklets left panel GAS IDE
Last active February 6, 2023 17:59
Four bookmarklets to toggle / shrink / resize the left panel (and top section) of the Google Apps Script IDE
View Bookmarklets left panel GAS IDE
// Create a new bookmark in your browser using any of these URLs | @pfelipm 06/02/23
// Demo: https://twitter.com/pfelipm/status/1622395953721364481
// 1. Toggles the side panel out of view
javascript:(function() {const style = document.querySelectorAll('div[jsname="Iq1FLd"]')[0].style;if (style.display == 'none') style.display = '';else style.display = 'none';})()
// 2. Allows the side panel to shrink down to 150px when resizing window
javascript:(function() {const style = document.querySelectorAll('div[jsname="Iq1FLd"]')[0].style;if (style.minWidth == "150px") style.minWidth = "225px";else style.minWidth = "150px";})()
// 3. Resizes the side panel to the width specified by a JS prompt
@pfelipm
pfelipm / fxParseJson.gs
Last active December 16, 2022 09:00
PARSEJSON Apps Script custom function for Google Sheets
View fxParseJson.gs
/**
* Parses a JSON string and returns a single element designated by its full path.
* Mimics Coda's ParseJSON (https://coda.io/formulas#ParseJSON) formula.
* @param {A2:A3} jsonData Source JSON string or data interval containing JSON strings to parse.
* @param {A5:A6} fullPath Path string or data interval of paths to use for extraction, use ".number" for array elements.
* @param {false} stringify Optional, false (default) if result should be stringified only if it is an object.
* @return Extracted primitive value or stringified representation, if array or object.
*
* @customfunction
*
View Check user onOpen workflow.gs
/**
* Demo authorization workflow that checks whether the current user can run this Apps Script, uses
* the Admin SDK Directory Advanced Service
* Twitter thread here: https://twitter.com/pfelipm/status/1576954653425618945
* @pfelipm (OCT/22)
*
* @OnlyCurrentDoc
*/
@pfelipm
pfelipm / Reset slicers.gs
Last active November 23, 2021 17:04
Reset all slicers in the current sheet of a Google Spreadsheet
View Reset slicers.gs
/**
* Resets all slicers in the active sheet using the Apps Script Spreadsheet Service.
*
* Context:
*
* Calling setColumnFilterCriteria(columnPosition, null) or trying to modify
* in some other way the current filter criteria of a slicer whose criteria
* has already been set manually (using the GUI) won't have any effect.
* So, this function clones & deletes all slicers, instead.
*
View getGroupsRecursively.gs
/**
* Recursively gets all groups that a given user/group is a member
* of, either directly or indirectly, in a GWS domain.
*
* -OR-
*
* checks whether user/group is a member of provided group,
* both tasks support external users to the domain.
* Uses the AdminDirectory advanced service, and should be invoked by domain Admins!
*
View TOC custom fx.gs
/**
* Builds a TOC in the spreadsheet.
*
* Pablo Felip (@pfelipm).
*
* @param {true} includeId Include sheet ID
* @param {true} includeUrl Include link to sheet
* @return List of [Sheet name, Sheet ID, Sheet URL]
* @customfunction
*/
@pfelipm
pfelipm / fxRepetirFilas.gs
Last active May 23, 2021 22:27
Función personalizada para HdC repetirFilas()
View fxRepetirFilas.gs
/**
* Repite cada fila un nº indicado de veces
* @param {A2:C4} intervaloDatos Intervalo de datos cuyas filas se van a repetir
* @param {D2:D4} colRepeticiones Vector columna que indica el nº de veces a repetior cada fila
* @customfunction
*/
function repetirFila(intervaloDatos, colRepeticiones) {
// ⚠️ ¡Se debería realizar un control de errores sobre los parámetros de la función!
@pfelipm
pfelipm / ButtonLock.gs
Last active May 9, 2021 00:25
Using LockService to avoid concurrent executions, specially relevant when invoking GAS code from buttons in a spreadsheet.
View ButtonLock.gs
/**
* Simple demonstration of LockService...
* https://developers.google.com/apps-script/reference/lock
* ...to prevent concurrent executions of a GAS function,
* for example when invoked by a button in a Spreadsheet (rather likely).
* Demo: https://docs.google.com/spreadsheets/d/1n_7nZIi5QtN9URzsKdFW6WtSxPM9uUK3QhQSbzbltHg
*
* @OnlyCurrentDoc
*/
@pfelipm
pfelipm / generated.photos.gs
Last active April 28, 2021 07:22
Get faces generated from scratch by AI using generated.photos API
View generated.photos.gs
/**
* generated.photos API demo >> Faces generated from scratch by AI
*
* 1. Get API key at https://generated.photos/api
* 2. Make a copy of https://docs.google.com/spreadsheets/d/1RkLy6e0h1QURtsOj1gsAfWGzvAUua0yIky_THy8ut1Q
* 3. Write your API key inside cell C3 (don't share!)
*
* Please, note:
* - Just 50 API calls/month! (API free plan)
* - Some trait combinations apparently fail to return results :-?
@pfelipm
pfelipm / Text2GzipBlob.gs
Last active May 10, 2021 06:46
From UNICODE text to gzipped blob encoded as text and back using Apps Script
View Text2GzipBlob.gs
/**
* These two functions:
* 1. Transform a UNICODE string to blob, compress and encode it as text using two different methods (base64 or JSON.stringify)
* 2. Reverse de process
*/
function testTextoGzipBlob() {
// Method 1: base64Encode (better compression):
// Text >> Blob >> gzip >> getBytes Byte[] >> *base64Encode* text >> [cache key/value] >> *base64Decode* Byte[] >> Blob >> ungzip >> Text 😅