I wrote quite a lot of (custom) functions to extend Google Sheets with some functionality or exploid some API. Mostly on Reddit (r/GoogleAppsScript / r/googlesheets / r/sheets). So i thought to create an code base for functions that could be usefull for others as well.
Almost all functions have a custom menu in there. I you are using multiple, you need merge the .addItem(). There is also the option to invoke the 'not custom functions' via an shortcut:
- Tools -> Macro -> Import -> Choose function.
- Tools -> Macro -> Manage -> Assign shortcut.
| Script | Tags | ReadMe / Invoke | Sample sheet | Description |
|---|---|---|---|---|
| AlphaVantage | API, Finanacial, Stock | - | - | Get information from AlphaVantage |
| ChangeHyperlinkName | Sheets, Utility | - | - | Sets the name of the selected =HYPERLINK() formula's to the same name. |
| CoinMarketCap | API, Finanacial, Stock | - | - | Get your API key. Gets the top 5000 coins in your speadsheet. |
| ConnectedSheetValue | Custom function | =CONNECTED_SHEET_VALUE(range,direction) |
- | Get values from the sheet to the left or to the right. |
| ConvertDateTime | Custom function | =CONVERTDATETIME(range, timezone, format) |
- | Converts (multiple) columns to the timezone you enter. You also can give up your formatting. |
| CountColorAndValue | Custom function | =COUNT_COLOR_VALUE(F3:G10,GET_BACKGROUND(F3),"RemcoE33") |
Sheet | Count values that meets the value and background color critera. |
| Dictionary | Sheets, Utility, Custom functions | =DICTIONARY(words, synonyms) |
- | Get definition and/or synonyms from a word |
| DriveImage2Sheets | Drive, Image | ReadMe | - | Get images from specific folder on your drive and insert =IMAGE() formula's. Option: only get the download link. Option: Create a checkbox to activate the =IMAGE() formula. |
| EnlargeImage | Sheets, Utility | - | - | Active a cell that contains an =IMAGE() formula. Invoke the function via Marcos or shortcut (after installing the shortcut via macros -> manage) |
| Fastqoute | API, Finanacial, Stock | - | Get fastqoute data to sheets. | |
| FinalUrl | URL fetch | - | - | Gets final redirected url from a url. |
| FMcloud | API, Finanacial, Stock | - | Sheet | Get data from FMcloud |
| GetFilesInfoFromDriveFolder | Drive | - | - | Gets info form all the files inside a specific drive folder. |
| GetFinanacialTransscripts | API, Finanacial, Stock, Custom function | =GETTRANSCRIPT("ticker",periode,year,"apikey") |
- | Needs API key. Get transscript from specific ticker. Can be invoked via custom formula or via trigger inside script editor. docs |
| IMDB | API | - | Sheet | IMDB docs to get your token. This sheet will load the top250 movies and tv series, plus the option to find movies between two actors. |
| INC500 | API | - | - | Get the 500 companys from INC to your sheet. |
| LogSheetOpenings | onOpen | - | - | Counts the number of times the sheets is opend (excl. yourself) |
| NTranspose | Sheets, Utility | =NTRANSPOSE(range, number) |
- | Transposes a single column every n number of rows |
| NumberShortHandToRealNumber | onEdit(e) | - | - | Type '1K' and the scripts set numberformat to '1K' but the value to 100000 (1k/1m/1b) |
| OutOfOffice | Gmail | - | - | Sets and unset the Gmail out-of-office response based on your ow configured (time) triggers. Example: 17:00 ON --> 09:00 OFF. |
| SplitConcat | Custom function | =SPLIT_CONCAT(range, columnNumberOfValuesToSplit, delimiter) |
Sheet | Splits values in one column and makes a copy of the row for each split value. |
| SumAllCells | Custom function | =SUM_ALL_CELLS(cellToSum, {'SheetToExclude1",SheetToExclude2"}) |
- | Sums the given cell (A1) over all the sheets in the Spreadsheet. |
| WalletCoins | API, Finanacial, Stock | - | - | Get coin name, shortname, rate and icon into sheets. JSON data |
| Ychart | API, Finanacial, Stock, Custom function | =YCHARTS("AAPL") |
- | Gets raw data from the Ychart chart. JSON data |
| ZipChecker | API | =ZIPCHECKER(zip, countryISO2) |
- | Get information from a zipcode. Example |