Matthew Hait Matthew Hait - 1 year ago 89
Javascript Question

JavaScript variable missing value

I'm making a google sheet that writes to a SQL server periodically. This is my code so far, less the SQL stuff. It grabs the a variable from the last entry in the database ("User" in this test), and submits everything after it.

function connectToMySqlDB() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
var lastColumn = sheet.getLastColumn();
Logger.log('Starting query process... ');

// Connect to DB and get last entry
var lastline = "User"
Logger.log('Found serial number of last enter in SQL Database: ' + lastline);

// Find row of entry in goolge sheet
for (var i = 0; i < values.length; i++) {
var row = "";
for (var j = 0; j < values[i].length; j++) {
if (values[i][j] == lastline) {
row = [i+1]
Logger.log('Found serial number in row: ' + row);

// Get first unfilled row
var crange = sheet.getRange("C1:C").getValues();
var Clast = crange.filter(String).length;
Logger.log('Found last row in C: ' + Clast);
var cfin = [Clast - row]
Logger.log('Slection will be: ' + row + ' rows.');

// select rows from entry to last filled row
var selection = sheet.getRange(row,0,cfin,lastColumn)

// Submit rows line by line to DB (from last entry to first unfilled)

// Write one row of data to a table.


The error I get from the google error is:

The coordinates or dimensions of the range are invalid. (line 33, file "Code")

Using the debug tool, the variable "row" has no value. The log file however shows that it does work

[16-09-15 11:39:41:063 PDT] Starting query process...
[16-09-15 11:39:41:064 PDT] Found serial number of last enter in SQL Database: User
[16-09-15 11:39:41:067 PDT] Found serial number in row: 26
[16-09-15 11:39:41:157 PDT] Found last row in C: 37
[16-09-15 11:39:41:158 PDT] Selection will be: rows.

My guess is somehow "var cfin = [Clast - row]" is clearing the "row" variable. I can't figure out for the life of me how to get around this.

Answer Source

You seem to want row to be a number, but you're wrapping its calculation in square braces, giving you an array of one element long. Remove the square braces.

row = i+1;

(As an aside, you're declaring it as a string, wont make a difference but its better to not do that!)

After that, you're making the same mistake with cfin. Dont wrap it in square braces if you simply want a number

var cfin = Clast - row;

You may also have a problem if row is set by being found, but then the outer loop will reset that variable. You can get round this by breaking out of both loops:

var row = -1;
for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {     
      if (values[i][j] == lastline) {
        row = i+1
        Logger.log('Found serial number in row: ' + row);
    if(row > -1) break;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download