Getting "undefined" when trying to get values from spreadsheet

Question

I'm getting an "undefined" error when I try to get the value from a cell in a spreadsheet. The thing is that if I execute the same command for a different cell I get the value in that cell. The only difference between those 2 cells is the way the value is produced.

The value in the cell that show correctly is produced directly from the Google Form associated with that spreadsheet. The value that doesn't show when called, is produced from a script I created in the Google Form.

Script for the Form (triggered on form submit):

// This code will set the edit form url as the value at cell "C2".

function assignEditUrls() {
    var form = FormApp.getActiveForm();
    var ss = SpreadsheetApp.openById("my-spreadsheet-id")
    var sheet = ss.getSheets()[0];

    var urlCol = 3; // column number where URL's should be populated; A = 1, B = 2 etc
    var formResponses = form.getResponses();

    for (var i = 0; i < formResponses.length; i++) {
        var resultUrl = formResponses[i].getEditResponseUrl();
        sheet.getRange(2 + i, urlCol).setValue(resultUrl); 
    } 
    SpreadsheetApp.flush();
 }

Table (changed to HTML)

<table>
    <tr> <!-- Row 1 -->
        <td>Timestamp</td> <!-- A1 -->
        <td>Name</td> <!-- B1 -->
        <td>Edit form URL</td> <!-- C1 -->
    </tr>
    <tr> <!-- Row 2 -->
        <td>5/26/2015 14:04:09</td> <!-- A2: this value came from the form submittion-->
        <td>Jones, Donna</td> <!-- B2: this value came from the form submittion-->
        <td>https://docs.google.com/forms/d/1-FeW-mXh_8g/viewform?edit2=2_ABaOh9</td> <!-- C2: this value came from the the script in the form -->
    </tr>
</table>

Script in Spreadsheet (Triggered on form submit)

function onFormSubmit(e) {

    // This script will get the values from different cells in the spreadsheet 
    // and will send them into an email.

    var name = e.range.getValues()[0][1]; // This will get the value from cell "B2".
    var editFormURL = e.range.getValues()[0][2]; // This will get the value from cell "C2".

    var email = '[email protected]';
    var subject = "Here goes the email subject."
    var message = 'This is the body of the email and includes'
                  + 'the value from cell "B2" <b>'
                  + name + '</b>. This value is retrieved correctly.' 
                  + '<br>But the value from cell "C2" <b>'+ editFormURL
                  + '</b> show as "undefined".';

    MailApp.sendEmail(email, subject, message, {htmlBody: message});
}

The email looks like this:

Sented by: [email protected]

Subject: Here goes the email subject.

Body:

This is the body of the email and includes the value from cell "B2" Jones, Donna. This value is retrieved correctly.

But the value from cell "C2" undefined show as "undefined".

Question:

What am I doing wrong?

Answer 1

You've most likely got a race condition in play.

1. A user submits a form. This is our prime event.

2. Upon form submission, all triggers that are associated with the event are fired.

   • assignEditUrls() in the form script, and

   • onFormSubmit() in the spreadsheet script.

   If you had other scripts set up for this event, they would also trigger. The complication here is that all those triggers are fired independently, more-or-less at the same time, but with no guaranteed order of execution. The spreadsheet trigger MIGHT run BEFORE the form trigger! So that's one problem.

Each trigger will receive the event information in the format specific to their definition. (See Event Objects.) Since C2 is not actually part of the form submission, it won't be in the event object received by the spreadsheet function. That's your second problem, but since you know the offset of the value relative to the form input, you can use range.offset() to get it.

An additional wrinkle has to do with the way that Documents and Spreadsheets are shared; each separate script invocation will receive its own copy of the Spreadsheet, which is synchronized with other copies... eventually. Changes made to the spreadsheet by one script will not be immediately visible to all other users. And that makes three problems.

What to do?

You could try to coordinate operations of the two related trigger functions. If they're in the same script, the Lock Service could help with this.

You could have just one trigger function to perform both operations.

Or you could make the spreadsheet function tolerant of any delays, by having it wait for C2 to be populated. This snippet would do that...

...
var editFormURL = null;
var loop = 0;
while (!editFormURL) {
  editFormURL = e.range.offset(0,2).getValue(); // This will get the value from cell "C2".
  if (!editFormURL) {
    // Not ready yet, should we wait?
    if (loop++ < 10) {
      Utilities.sleep(2000); // sleep 2 seconds
    }
    else throw new Error( 'Gave up waiting.' );
  }
}

// If the script gets here, then it has retrieved a value for editFormURL.
...

One bonus problem: since you're using getValues(), with the plural s, you are retrieving 2-dimensional arrays of information. You're not seeing a problem because when you treat those values like a string, the javascript interpreter coerces the array into the string you've wished for. But it is still a problem - if you want a single value, use getValue().

Answer 2

Get the correct row, then hard code the column with your URL:

var ss = SpreadsheetApp.openById("my-spreadsheet-id")
var sheet = ss.getSheets()[0];

var rowForLookup = e.range.getRow();
var columnOfUrl = 24; //Column X
var theUrl = sheet.getRange(rowForLookup, columnOfUrl).getValue();

Answer 3

So this code is what I finally got after implementing your recommendations. Thanks to Sandy Good and Mogsdad.

function onFormSubmit(e) {

    Logger.log("Event Range: " + e.range.getA1Notation());

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    var startRow = e.range.getRow();
    var startCol = 1;
    var numRows = 1;
    var numColumns = sheet.getLastColumn();

    var dataRange = sheet.getRange(startRow, startCol, numRows, numColumns);
    var data = dataRange.getValues();

    Logger.log("Data Range: " + dataRange.getA1Notation());


    for (var i = 0; i < data.length; ++i) {
        var column = data[i];
        var name = column[4];

        var editFormURL = null;
        var loop = 0;

        while (!editFormURL) {
            editFormURL = column[23];
            if (!editFormURL) { 
                // Not ready yet, should we wait?
                if (loop++ < 10) {
                    Utilities.sleep(3000); // sleep 2 second
                }
                else throw new Error( 'Gave up waiting.' );
            }
        }    

        var email = '[email protected]';
        var subject = "Subject here";
        var message = 'Some text about ' + name + '.' +
                      '<br><br>Please view this link: ' + 
                      + editFormURL;

        MailApp.sendEmail(email, subject, message, {htmlBody: message});
    }
}