Google导入不工作，应用程序脚本无法从单元格读取数据

Question

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

我尝试了多种方式获取单元格数据：ss.getSheetByName（'Sheet1'）. getRange（'L1'）. getValue（）我还创建了一个数组，方法是使用一个范围和该数组中的数据。两种方式都可以工作一段时间，它会持续几个小时没有问题，然后就不能读取单元格内容。

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

//如果我能做到这一点，剩下的代码就可以工作了。我经常收到抽签通知，抽签是空白的，或者参展商是空白的。

我希望每次都是数字。但是当它不起作用时，它们都是空白的。

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