Skip to content

Instantly share code, notes, and snippets.

@johnvilsack
Created August 22, 2019 14:59
Show Gist options
  • Save johnvilsack/5679982d8f843eee819d1ab9cab5bf72 to your computer and use it in GitHub Desktop.
Save johnvilsack/5679982d8f843eee819d1ab9cab5bf72 to your computer and use it in GitHub Desktop.
GAS- Full Row Scoping
// 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