[SOLVED] Import Data to Ubidots from Google Sheets, TypeError: Cannot call method "getDataRange" of null.ERROR!

Ubidots API
,
1

Hello, I am trying to import my old data from google sheets into ubidots. I am following this article. https://help.ubidots.com/developer-guides/import-data-to-ubidots-from-google-sheets

But when I run the script I get the error, TypeError: Cannot call method “getDataRange” of null

Following is the part of the script.

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Send Data', functionName: 'sendData'}
  ];
  sheet.addMenu('Ubidots', menuItems);
}
        
function sendData() {
  var sheetApp = SpreadsheetApp.getActive();
  var sheet = sheetApp.getSheetByName("Data");
  var data = sheet.getDataRange().getValues();
  var payload = {};

  for (var i = 1; i < data.length; i++) {
    var date = new Date(data[i][0]);
    var timestamp = date.getTime();

    for (var j = 1; j < data[0].length - 1; j++){
      payload[data[0][j]] = {"value": data[i][j], "timestamp":   
           timestamp};
    }
    
    var status = send_to_ubidots(DEVICE_LABEL, payload);
    
    if (status == null){ 
     sheet.activate().getDataRange().getCell(i +  
           1,6).setValue("Couldn't send data");
    }
   else{
      sheet.activate().getDataRange().getCell(i + 
           1,6).setValue("Sent");
   }
  }
}

I read the forums and I see many of us are getting this error I tried a few things that was mentioned in the forums like…but didnt work!

var ss = SpreadsheetApp.openById(“Your Sheet ID here”);
var sheet = ss.getSheetByName(“Name of your Sheet”);

. Was anyone able to solve this issue?

Thank you

2

Greetings, your issue seems related to the native Google’s method sheet.getDataRange() rather than with Ubidots. The method seems to retrieve data from a NULL reference, so it throws you an exception. Make sure that you are setting properly your sheet name using the method getSheetByName(), for the example of the article the sheet name is ‘data’, make sure to change it to fit yours.

If you still experience issues, please reference to the official Google’s docs to find additional hints.

All the best

3

Hi @jotathebest I made a couple of tweaks in the code mentioned in the article. and now I am able to successfully import old data to ubidots. Thank you