Google Sheets间歇性IMPORTRANGE无法工作，应用程序脚本无法从单元格读取数据

Question

我最大的问题是偶尔我的google sheets应用程序脚本无法读取单元格的内容。它将停止工作5-30分钟，然后重新开始工作。这是一个实时的记分表，所以这是一个很大的问题，不能为相当大的时间块保存数据。

我尝试过以多种方式获取单元格数据: ss.getSheetByName('Sheet1').getRange('L1').getValue()我还通过对某个范围使用getValues()并使用该数组中的数据创建了一个数组。这两种方式都会工作一段时间，几个小时后就不会出现问题，只是无法读取单元格的内容。

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{
}

//如果我走到这一步，剩下的代码就可以工作了。我经常收到Draw #为空的警告，或者Exhibitor #为空。

我希望drawNumb和exhNum每次都是数字。但是当它不工作时，它们都是空白的。

Answer 1

这对我来说很有效：

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.');
  }
}