Google Sheets script hangs on getRange

Question

POST UPDATE: Hi, at the end the problem was seemingly not related to the getvalue() itself but to the source, which was a range imported by an IMPORTRANGE formula. This formula was bugging. I do not really know why but I assume it was somehow related to the size of the spreadsheet. I have not been able to check this point. I tried to download the file without success. Finally, I have build a new spreadsheet. Things now seem to be working alright now. But still I’d like to now what happened to understand how to avoid it next time. Anyway, thank you for your help.

ORIGINAL POST:

I experiencing problems with the getRange function in GAS. It's quite a simple code which was working normally until something (I do not know what) went wrong and then started to fail. I am just trying to gather a date value from a cell to use it afterwards to create an email.

I am trying to debug the code step by step by means of Logger and vars but I am struggling to figure out what happens when I try to getValue from the corresponding monocell range.

This below is the code I am trying currently:

// Retreive email receipts from Form submitted choice

function EmailUpdatefromForm()
{

  var spreadsheet = SpreadsheetApp.openById("1ImOXXXXXxSDz8AqjMWtuSPtg7xMejqPpHQ9vwDnY"); 

  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();

  var formlength = formResponses.length;
  Logger.log(formlength);

    var formResponse = formResponses[formResponses.length-1];
    var itemResponses = formResponse.getItemResponses();

    var itemlength = itemResponses.length;
    Logger.log(itemlength);    

    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      var emailto = itemResponse.getResponse();
      Logger.log(itemResponse+" - "+emailto);
    }

  var email = (emailto? emailto[0] : "[email protected]");
  var sheet = spreadsheet.getSheetByName("report");

  var log1 = spreadsheet.getId(); //--> THIS CHECKPOINT WORKS AND GETS THE PROPER SS ID
  var log2 = sheet.getSheetId(); //--> THIS CHECKPOINT WORKS AND GETS THE PROPER SHEET ID
  var log3 = sheet.getRange(2, 1) //--> THIS CHECKPOINT WORKS ALTHOUGH I CANNOT SEE IF IT IS ACTUALLY POINTING TO THE PROPER CELL

  var log4 = sheet.getRange(2, 1).getValues(); //--> THIS CHECKPOINT GETS STUCK\\


  var updatedate = sheet.getRange(2,1).getValue(); //--> THIS IS WHAT I REALLY WANT TO DO
  var PDFdate = Utilities.formatDate(updatedate, "GMT+1", "yyyyMMdd")

}

I have found several posts mentioning issues with getRange but I have not found any response really clarifying what's happening here and how to turn it around.

Thank you in advance for your support!

Have you tried calling it to a different cell, and if that also fail, could you possibly show a sanitized version of your sheet? also, how large is it? — AMolina
Thanks. Post updated. The problem seemed to away, not a getvalue() issue at the end. — Omar Vieira
As far as I created a full new spreadsheet and it is not yet as large as its previous version was, the answer is NO, the problem is not affecting me anymore. Thank you. — Omar Vieira

Cooper Cooper · Accepted Answer · 2019-08-22T15:24:05

function EmailUpdatefromForm() {
  var spreadsheet = SpreadsheetApp.openById("1ImOXXXXXxSDz8AqjMWtuSPtg7xMejqPpHQ9vwDnY"); 
  var form = FormApp.getActiveForm();
  var formResponses = form.getResponses();
  var formlength = formResponses.length;
  Logger.log(formlength);
  var formResponse = formResponses[formResponses.length-1];
  var itemResponses = formResponse.getItemResponses();
  var itemlength = itemResponses.length;
  Logger.log(itemlength);    
  for (var j = 0; j < itemResponses.length; j++) {
    var itemResponse = itemResponses[j];
    var emailto = itemResponse.getResponse();
    Logger.log(itemResponse+" - "+emailto);
  }
  var email = (emailto? emailto[0] : "[email protected]");
  var sheet = spreadsheet.getSheetByName("report");
  var log1 = spreadsheet.getId();
  var log2 = sheet.getSheetId();
  var log3 = sheet.getRange(2, 1);
  var log4 = log3.getValue();
  var updatedate = log4;
  var PDFdate = Utilities.formatDate(updatedate, "GMT+1", "yyyyMMdd")
}

Google Sheets script hangs on getRange

1 Answers