Created
August 22, 2019 14:59
Star
You must be signed in to star a gist
GAS- Full Row Scoping
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// This script runs every time a cell is changed | |
function onEdit(e) { | |
var thisSheet = SpreadsheetApp.getActiveSheet(); | |
var editedCell = e.range.getA1Notation(); | |
var editedRow = e.range.getRow(); | |
var editedCol = e.range.getColumn(); | |
var editedA1Col = columnToLetter(editedCol); | |
// @@ DEBUG | |
thisSheet.getRange('I10').setValue(editedCell); | |
thisSheet.getRange('I11').setValue(editedRow); | |
thisSheet.getRange('I12').setValue(editedCol); | |
thisSheet.getRange('I13').setValue(editedA1Col); | |
// @! DEBUG | |
// evalRow(thisSheet, editedRow); | |
evalRow(); | |
} | |
//function evalRow(thisSheet, editedRow) { | |
// @@ DEBUG | |
function evalRow() { | |
var thisSheet = SpreadsheetApp.getActiveSheet(); | |
var editedRow = 1; | |
var debugRow = 30; | |
// @! DEBUG | |
// Range to go after | |
var rangeTarget = "A"+editedRow+":E"+editedRow; | |
// Collect the data | |
var rangeArray = thisSheet.getRange(rangeTarget); | |
// Dump it from the object | |
var rangeValue = rangeArray.getValues(); | |
// The "Object" has one record we care about. Instead of double loop, just declare and be done with it | |
for (var i in rangeValue[0]) { | |
// Loop custom function here | |
// @@ DEBUG | |
Logger.log(rangeValue[0][i]); | |
thisSheet.getRange('I'+debugRow).setValue(rangeValue[0][i]); | |
debugRow = debugRow + 1; | |
// @! DEBUG | |
} | |
} | |
// Fixes SMALL oversight of not being able to work with A1 notation | |
function columnToLetter(column) | |
{ | |
var temp, letter = ''; | |
while (column > 0) | |
{ | |
temp = (column - 1) % 26; | |
letter = String.fromCharCode(temp + 65) + letter; | |
column = (column - temp - 1) / 26; | |
} | |
return letter; | |
} | |
function letterToColumn(letter) | |
{ | |
var column = 0, length = letter.length; | |
for (var i = 0; i < length; i++) | |
{ | |
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1); | |
} | |
return column; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment