Google Sheets Intermittent IMPORTRANGE not working and app script is unable to read the data from cells

Question

My biggest issue is occasionally my google sheets app script cannot read the contents of cells. It will stop working for 5-30 minutes, then start working again. This is a live scoring sheet so that is a big problem not being able to save data for for pretty large chunks of time.

I have tried getting the cell data in multiple ways: ss.getSheetByName('Sheet1').getRange('L1').getValue() I also created an array by using getValues() for a range and using the data from that array. Both ways work for a while, it will go hours without an issue then just not be able to read the cell contents.

function SaveScore(btn) {
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // ss is now the spreadsheet the script is associated with
  //var ss = SpreadsheetApp.openById('1rKzGRC7gAWKc1m4yrEDYlospgE-JcAxngV-70rNTbTs').getSheetByName('Announcer');

  var scoreData = ss.getSheetByName('Announcer').getRange('A1:Q7').getValues();
  //var curDrawRow = ss.getSheetByName('Setup').getRange('I11').getValue();
  var curDrawRow = scoreData[4][16];


  //var scoreJ1 = scoreData[1][1];
  //var scoreJ2 = scoreData[1][2];
  //var scoreJ3 = scoreData[1][3];
  //var scoreTotal = scoreData[1][4];
  //var classNum = scoreData[0][11];

  //var drawNumb = scoreData[3][0];
  //var exhNum = scoreData[3][8];

  var curOffset = scoreData[6][16];
  //var curOffset = ss.getSheetByName('Announcer').getRange('Q7').getValue();
  //var scoreJ1 = ss.getSheetByName('Announcer').getRange('B2').getValue();
  var scoreJ1 = scoreData[1][1];
  //var scoreJ2 = ss.getSheetByName('Announcer').getRange('C2').getValue();
  var scoreJ2 = scoreData[1][2];
  //var scoreJ3 = ss.getSheetByName('Announcer').getRange('D2').getValue();
  var scoreJ3 = scoreData[1][3];
  //var scoreTotal = ss.getSheetByName('Announcer').getRange('E2').getValue();
  var scoreTotal = scoreData[1][4];
  //var classNum = ss.getSheetByName('Announcer').getRange('L1').getValue();
  var classNum = scoreData[0][11];
  //var drawNumb = ss.getSheetByName('Announcer').getRange('Raw Data!A' + (curDrawRow + curOffset)).getValue();
  var drawNumb = scoreData[3][0];
  //if (drawNumb == '') {
  //  var drawNumb = ss.getSheetByName('Raw Data').getRange('A' + (curDrawRow + curOffset)).getValue();
  //}
  //if (drawNumb == '') {
  //  var drawNumb = ss.getSheetByName('Announcer').getRange('A4').getValue();
  //}
  //var exhNum = ss.getSheetByName('Announcer').getRange('Raw Data!B' + (curDrawRow + curOffset)).getValue();
  var exhNum = scoreData[3][8];

  //if (exhNum == '') {
  //  var exhNum = ss.getSheetByName('Raw Data').getRange('B' + (curDrawRow + curOffset)).getValue();
  //}
  //if (exhNum == '') {
  //  var drawNumb = ss.getSheetByName('Announcer').getRange('I4').getValue();
  //}

  if (scoreTotal == '--' && btn != 'review') {
    ui.alert('Please enter a score');
    ss.getActiveSheet().setActiveRange(ss.getActiveSheet().getRange('B2'));
  }else if (classNum == '') {
    ui.alert('Class # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }else if (drawNumb == '') {
    ui.alert('Draw # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }else if (exhNum == '') {
    ui.alert('Exhibitor # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }else{
}

// if I get this far the rest of the code will work. I often get the alert for Draw # is blank, or Exhibitor # is blank.

I expect drawNumb and exhNum to be numbers every time. But when it's not working, they are both blank.

Try to replace ss.getSheetByName('Announcer').getRange('A1:Q7').getValues(); by ss.getSheetByName('Announcer').getDataRange.getValues(); — St3ph
*Also ss.getSheetByName('Announcer').getRange('Raw Data!A' + (curDrawRow + curOffset)).getValue(); is not valid the Raw Data is a sheet name so it must be ss.getSheetByName('Raw Data').getRange('A' + (curDrawRow + curOffset)).getValue() the function getValue() consume lot of time to be treated so that is true it is better to use a big table using getDataRange than multiplying the getValue() — St3ph
Could you please share a copy of the Sheets? Removing any private/personal information of course. — Andres Duarte

Cooper Cooper · Accepted Answer · 2019-10-08T14:56:42

This works for me:

function runOne(btn) {
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // ss is now the spreadsheet the script is associated with
  var scoreData = ss.getSheetByName('Announcer').getRange('A1:Q7').getValues();
  var curDrawRow = scoreData[4][16];
  var curOffset = scoreData[6][16];
  var scoreJ1 = scoreData[1][1];
  var scoreJ2 = scoreData[1][2];
  var scoreJ3 = scoreData[1][3];
  var scoreTotal = scoreData[1][4];
  var classNum = scoreData[0][11];
  var drawNumb = scoreData[3][0];
  var exhNum = scoreData[3][8];
  if (scoreTotal == '--' && btn != 'review') {
    ui.alert('Please enter a score');
    ss.getActiveSheet().setActiveRange(ss.getActiveSheet().getRange('B2'));
  }else if (classNum == '') {
    ui.alert('Class # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }else if (drawNumb == '') {
    ui.alert('Draw # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }else if (exhNum == '') {
    ui.alert('Exhibitor # is blank. This is a google syncing issue and typically will fix itself in about 5-10 minutes. Please wait and try again in 5-10 minutes.');
  }
}

Google Sheets Intermittent IMPORTRANGE not working and app script is unable to read the data from cells

1 Answers