Loop intelligently.
function myFunction() {
/* The Classic For Loop */
for (let i = 0; i < 3; i++) {
// DO: Use for repeating a task multiple times.
SpreadsheetApp.getActiveSpreadsheet().insertSheet(`NewSheet${i+1}`)
// DON'T: Use for handling arrays.
const currentValue = myArray[i][1]
if (currentValue === `TARGET`) {
Logger.log(`FOUND`)
}
}
// Trying to find an index of an array? => array.findIndex()
// Trying to filter out unwanted items in array? => array.filter()
// Trying to change each element in an array? => array.map()
// Trying to repeat a task for each item in an array? => array.forEach()
// See: "Use Array Methods" or search "Array Methods"
}
For others, and your future self.
/** EXAMPLE A */
function myFunction() {
var s = SpreadsheetApp.getActiveSpreadsheet()
var sht = s.getSheetByName('Sheet1')
var drng = sht.getDataRange()
var rng = sht.getRange(2,1, drng.getLastRow()-1,drng.getLastColumn())
var rngA = rng.getValues()
var rngB = []
var b = 0
for(var i = 0; i < rngA.length; i++) {
if(rngA[i][1] == 'TARGET')
{
rngB[b]=[]
rngB[b].push(rngA[i][0],rngA[i][2])
b++
}
}
var shtout = s.getSheetByName('Sheet2');
var outrng = shtout.getRange(shtout.getLastRow(),1,rngB.length,2);
outrng.setValues(rngB);
}
/** EXAMPLE B */
function myFunction() {
const inputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`Sheet1`)
const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`Sheet2`)
const inputSheetValues = inputSheet.getDataRange()
.getValues()
.filter(row => row[1] === `TARGET`)
.map(row => [row[0], row[2]])
return outputSheet.getRange(outputSheet.getLastRow()+1, 1, inputSheetValues.length, inputSheetValues[0].length)
.setValues(inputSheetValues)
}
Unless you plan on reusing variables, keep it simple.
/** EXAMPLE A */
function myFunction() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = spreadsheet.getSheets();
allSheets.forEach(function(sheet){
var sheetName = sheet.getName();
Logger.log(sheetName)
})
}
/** EXAMPLE B */
function myFunction() {
SpreadsheetApp.getActiveSpreadsheet()
.getSheets()
.forEach(i => {
Logger.log(i.getName())
})
}
STOP USING FOR LOOPS.
.entries(): For index tracking while iterating arrays.
const mySheet = SpreadsheetApp.getActiveSheet()
const row1Data = mySheet.getSheetValues(1, 1, 1, mySheet.getLastColumn()).flat()
for (let [index, item] of row1Data.entries()) {
Logger.log(`Item: ${item} is at index: ${index}.`)
}
.filter(): For filtering relevant data.
const mySheet = SpreadsheetApp.getActiveSheet()
const column1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const onlyNumbers = column1Data.filter(Number)
const onlyFilledCells = column1Data.filter(Boolean)
const onlyCellsWith5orMoreCharacters = column1Data.filter(cell => `${cell}`.length >= 5)
.findIndex(): For finding first item of array that matches criteria.
const mySheet = SpreadsheetApp.getActiveSheet()
const column1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const targetSubstringRowIndex = column1Data.findIndex(item => item.includes(`Assistant`))
.flat(): For flattening multidimensional arrays.
const mySheet = SpreadsheetApp.getActiveSheet()
const column1 = mySheet.getSheetValues(1, 1, mySheet.getLastRow())
// [[A1], [A2], [A3], ...]
const column1Flat = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
// [A1, A2, A3, ...]
.forEach(): For applying a function on each item.
const mySheet = SpreadsheetApp.getActiveSheet()
const row1Data = mySheet.getSheetValues(1, 1, 1, mySheet.getLastColumn()).flat()
row1Data.forEach((value, index) => Logger.log(`Col ${index}: ${value}`))
.includes(): For verifying a value exists.
const mySheet = SpreadsheetApp.getActiveSheet()
const row1Data = mySheet.getSheetValues(1, 1, 1, mySheet.getLastColumn()).flat()
if (row1Data.includes(`Manager`)) {
// ...
}
.indexOf() / .lastIndexOf(): For locating index of a value.
const mySheet = SpreadsheetApp.getActiveSheet()
const column1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const firstAppearance = column1Data.indexOf(`Bronze`)
const lastAppearance = column1Data.lastIndexOf(`Bronze`)
.join(): For joining array items by a string value.
const mySheet = SpreadsheetApp.getActiveSheet()
const row1Data = mySheet.getSheetValues(1, 1, 1, mySheet.getLastColumn()).flat()
const joined = row1Data.join(`-`)
.map(): For creating a new array from a function run on each array item.
const mySheet = SpreadsheetApp.getActiveSheet()
const sheetData = mySheet.getSheetValues(1, 1, mySheet.getLastRow(), mySheet.getLastColumn())
const onlyColumn2 = sheetData.map(row => row[1]).flat()
// Alternatively
const onlyColumn2Flat = sheetData.flatMap(row => row[1])
.pop() / .push(): For removing/adding last index to an array.
const list = [1, 2, 3, 4]
const removedLastItem = list.pop() // => 4
list.pop() // list = [1, 2]
list.push(5) // list = [1, 2, 5]
.reduce(): For consolidating an array to a single value.
const mySheet = SpreadsheetApp.getActiveSheet()
const col1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const sum = col1Data.reduce((a, b) => a + b, 0)
.shift() / .unshift(): For adding or removing first element of an array.
const list = [1, 2, 3, 4]
const removedFirstItem = list.shift() // => 1
list.shift() // list = [3, 4]
list.unshift(5) // list = [5, 3, 4]
.slice() / .splice(): For copying portion of an array, or removing/inserting and modifying original array.
const mySheet = SpreadsheetApp.getActiveSheet()
const sheetData = mySheet.getSheetValues(1, 1, mySheet.getLastRow(), mySheet.getLastColumn())
// sheetData untouched.
const onlyFirstTwoRows = sheetData.slice(0, 1)
const everyRowAfterTwo = sheetData.slice(1)
const lastTwoRows = sheetData.slice(-2)
// sheetData modified.
const insertRowAtIndexTwo = sheetData.splice(2, 0, Array(sheetData[0].length).fill(`ITEM`))
const replaceRowAtIndexTwo = sheetData.splice(2, 1, Array(sheetData[0].length).fill(`NEW ITEM`))
const removeFirstTwoRows = sheetData.splice(0, 2)
const removeAllRowsAfterTwo = sheetData.splice(2)
.some(): For testing if at least one item passes criteria.
const mySheet = SpreadsheetApp.getActiveSheet()
const col1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const targets = [`January`, `February`, `March`]
const col1HasTargetMonth = targets.some(item => targets.include(item))
.sort(): For sorting an array.
const mySheet = SpreadsheetApp.getActiveSheet()
const col1Data = mySheet.getSheetValues(1, 1, mySheet.getLastRow()).flat()
const col2Data = mySheet.getSheetValues(1, 2, mySheet.getLastRow()).flat()
const alphabeticalSort = col1Data.sort()
const numericSort = col2Data.sort((a, b) => a - b)