function sortSheet(sheet, column, direction) {
sheet.sort(column, new RegExp('asc', 'ig').test(direction) ? true : false)
}
// sortSheet(sheet, 2, 'Ascending')
// Sort by Column B ascending
range.sort(2)
// Sort by Column B ascending and then Column A ascending
range.sort([2, 1])
// Sort by Column B descending and then Column A ascending
range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}])
function basicJsonApiGetRequest(url) {
const result = JSON.parse(UrlFetchApp.fetch(url, {method: 'get'}).getContentText())
return result
}
// basicJsonApiGetRequest('https://rickandmortyapi.com/api')
function basicJsonApiPostRequest(url, payload) {
const result = JSON.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/json"}, method: 'post', payload: payload}).getContentText())
return result
}
// basicJsonApiPostRequest('https://dummy.restapiexample.com/api/v1/create', {name:"test",salary:"123",age:"23"})
function basicXmlApiPostRequest(url, payload) {
const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'post', payload: payload}))
console.log(result.getRootElement().getChildren().map(element => element.getText()))
return result
}
function basicXmlApiGetRequest(url) {
const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'get'}))
console.log(result.getRootElement().getChildren().map(element => element.getText()))
return result
}
function convertColumn(target, value) {
if (target == 'letter') {
let temp, letter = ''
while (value > 0) {
temp = (value - 1) % 26
letter = String.fromCharCode(temp + 65) + letter
value = (value - temp - 1) / 26
}
return letter
} else if (target == 'number') {
let column = 0, length = value.length;
for (i=0; i<length; i++) {
column += (value.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column
} else {
return 0
}
}
function convertJsonToArray(object) {
return new Array(Object.keys(object), Object.values(object))
}
function convertArrayToObjectArray(keys, data) {
let jsonArray = []
for(let item in data) {
let object = {}
let row = data[item]
for(let value in keys) {
let key = keys[value]
object[key] = row[value]
}
jsonArray.push(object)
}
return jsonArray
}
function convertObjectArrayToArray(objectArray) {
const finalArray = []
for(let item in objectArray) {
let object = objectArray[item]
if (item == 0) {
finalArray.push(Object.keys(object))
}
let array = []
for(let key in object) {
array.push(object[key])
}
finalArray.push(array)
}
return finalArray
}
function onOpen() {
// FormApp.getUi() // For Google Forms
// DocumentApp.getUi() // For Google Docs
// SlidesApp.getUi() // For Google Slides
SpreadsheetApp.getUi() // For Google Sheets
.createMenu('Custom Menu Option')
.addItem('Option One', 'functionName')
.addToUi()
}
function createLogFile() {
let snapshot = createSnapshot()
let sheetName = snapshot.sheetName
let dataBackup = snapshot.sheetData
let filename = `'${sheetName}' Snapshot - ${Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd HH:mm')}`
let log_file = SpreadsheetApp.create(filename);
log_file.getSheetByName('Sheet1').setName(`${sheetName} Snapshot`).getRange(1, 1, dataBackup.length, dataBackup[0].length).setValues(dataBackup);
DriveApp.getFileById(log_file.getId()).moveTo(getDataBackupFolder())
}
function filterOutBlankRows(data, targetColumnNum) {
const filteredData = data.filter((row) => row[targetColumnNum-1] !== "")
return filteredData
}
// filterOutBlankRows([['100', 'a'], ['101','b'], ['','c'], ['103','d']], 1)
function filterOutWithRegex(data, targetColumn, regexPattern, regexOptions) {
// For targetColumn, start from position 0
// For help with regex, try regex101.com
const regex = new RegExp(regexPattern, regexOptions)
const filteredData = data.filter((row) => {return regex.test(row[targetColumn]) == false})
return filteredData
}
// filterOutWithRegex([[1,'hi','HELLO'],[2,'bye','goodbye']], 2, /hello/, 'ig')
function getColumnByName(sheet, column_name, header_row) {
var data = sheet.getDataRange().getValues();
var col_index = data[header_row-1].indexOf(column_name);
return col_index+1
}
// getColumnByName(sheet, 'id', 1)
function diffArrays(array1, array2) {
array1 = array1.flat(Infinity), array2 = array2.flat(Infinity)
return [...new Set([array1.filter(x => !array2.includes(x)), array2.filter(x => !array1.includes(x))].flat())]
}
// diffArrays([1,2,3],[3,4,5])
function findMaxLengthOfNestedArrays(nestedArray) {
return Math.max(...nestedArray.map(row => row.length))
}
// findMaxLengthOfNestedArrays([[1,1,1],[2,2]])
function forLoopItems(data) {
for(let item in data) {
console.log(data[item])
}
}
// forLoopItems([1,2,3,4,5,6,7])
function forLoopIncremental(data, start, end) {
for(let i = start; i < end; i++) {
console.log(data[i])
}
}
// forLoopIncremental([1,2,3,4,5], 2, 4)
function getSheetData() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
let sheet = spreadsheet.getSheetByName('TARGET_SHEET_NAME')
let sheetData = sheet.getDataRange().getValues()
return sheetData
}
function createUniqueArray(data) {
return [...new Set(data)]
}
// createUniqueArray([1,1,2,3,4,5,5])
function getAllSheets() {
const spreadsheet = SpreadsheetApp.openByUrl(`${YOUR_SPREADSHEET_URL}`)
const sheets = spreadsheet.getSheets()
return sheets
}
// Array.splice(starting_position, positions_to_delete, new_value)
// injecting a new value
yourArray.splice(2,0,"new value")
// replacing an existing value
yourArray.splice(0,1,"new value")
function findRowIndexByTerm(lookupSheet, columnIndex, lookupValue) {
let values = lookupSheet.getDataRange().getValues()
let index = values.findIndex(row => row[columnIndex] === lookupValue)
return index + 1
}
function sendEmail(recipient, subject, emailBody) {
GmailApp.sendEmail(recipient, subject, emailbody)
}
// sendEmail('email@example.com', 'Awesome Subject', `Send this awesome email`)
function createBatchedArray(data, chunkSize) {
const result = []
for(let i=0; i<data.length; i+=chunkSize) {
let chunk = data.slice(i, i+chunkSize)
result.push(chunk)
}
return result
}