javascript - 电子表格中的 Google Apps 脚本错误

Question

从昨天开始，我们在将 REST API (urlfetch) 制作到 Zendesk 中时开始在 Google 电子表格中遇到错误。我们已经使用了 1.5 年，并没有遇到任何问题。它给出的错误是通用的——“遇到错误:很抱歉，发生服务器错误。请稍等一下，然后重试。”错误的屏幕截图 - http://support.prontomarketing.com/attachments/token/j1bjrnw1cpfu4tu/?name=2012-06-27_08-56-19.jpg

Google Apps Script Spreadsheet Services API 最近有什么变化吗？

有没有人有什么想法？

这是我们的脚本:(注意* zendesk 的 url 和 token 已被删除)

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
   var menuEntries = [ {name: "Create Tickets", functionName: "oneToMany"},{name: "Reset Form", functionName: "resetForm"} ];
   ss.addMenu("Actions", menuEntries);
}
function resetForm(){

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var currentIndex = doc.getActiveSelection().getRowIndex();

  var totalRow = findThelastRow('a',0);

  for (var count=2;count<totalRow;count++)
  {
      doc.getSheets()[0].getRange('a'+count).clear({contentsOnly:true});
      doc.getSheets()[0].getRange('h'+count).clear({contentsOnly:true});
      doc.getSheets()[0].getRange('h'+count).setBackgroundColor('#ffffff');
      doc.getSheets()[0].getRange('i'+count).clear({contentsOnly:true});
      doc.getSheets()[0].getRange('i'+count).setBackgroundColor('#ffffff');
      //doc.getSheets()[0].getRange('c'+count).setValue(clearA(doc.getSheets()[0].getRange('c'+count).getValue()));
  }

}
function continueRest(){
   var doc = SpreadsheetApp.getActiveSpreadsheet();
   var app = UiApp.createApplication().setTitle('Please Input Your Zendesk Username');
   app.setHeight('75');
   app.setWidth('400');
   // Create a grid with 3 text boxes and corresponding labels
   var grid = app.createGrid(1, 2);

   // Text entered in the text box is passed in to userName
   grid.setWidget(0, 0, app.createLabel('Username:'));
   var inputTextBox1 =  app.createTextBox().setName('username');
   inputTextBox1.setWidth('300px');
   grid.setWidget(0, 1,inputTextBox1 ); 

   // Create a vertical panel..
   var panel = app.createVerticalPanel();

   // ...and add the grid to the panel
   panel.add(grid);

   // Create a button and click handler; pass in the grid object as a callback element and the handler as a click handler
   // Identify the function b as the server click handler

   var button = app.createButton('submit').setId('button');
   var handler = app.createServerClickHandler('continueSentTheRest');
   handler.addCallbackElement(grid);
   button.addClickHandler(app.createServerClickHandler('pauseUI'));
   button.addClickHandler(handler);
   //button.setStyleAttribute('display', 'none');

   // Add the button to the panel and the panel to the application, then display the application app in the Spreadsheet doc
   panel.add(button);
   app.add(panel);
   doc.show(app);

}
function oneToMany(){
var doc = SpreadsheetApp.getActiveSpreadsheet();
   var app = UiApp.createApplication().setTitle('Please Input Your Zendesk Username');
   app.setHeight('75');
   app.setWidth('400');
   // Create a grid with 3 text boxes and corresponding labels
   var grid = app.createGrid(1, 2);

   // Text entered in the text box is passed in to userName
   grid.setWidget(0, 0, app.createLabel('Username:'));
   var inputTextBox1 =  app.createTextBox().setName('username');
   inputTextBox1.setWidth('300px');
   grid.setWidget(0, 1,inputTextBox1 ); 

   // Create a vertical panel..
   var panel = app.createVerticalPanel();

   // ...and add the grid to the panel
   panel.add(grid);

   // Create a button and click handler; pass in the grid object as a callback element and the handler as a click handler
   // Identify the function b as the server click handler

   var button = app.createButton('submit').setId('button');
   var handler = app.createServerClickHandler('authHandler');
   handler.addCallbackElement(grid);
   button.addClickHandler(app.createServerClickHandler('pauseUI'));
   button.addClickHandler(app.createServerClickHandler('mapValue'));
   button.addClickHandler(handler);
   //button.setStyleAttribute('display', 'none');

   // Add the button to the panel and the panel to the application, then display the application app in the Spreadsheet doc
   panel.add(button);
   app.add(panel);
   doc.show(app);
}
function pauseUI(e) {
  var app = UiApp.getActiveApplication();
  // try 'pausing' the app while we process the info
  app.setTitle('Please wait...');
  app.getElementById("button").setEnabled(false);
  return app;
}

var table = SpreadsheetApp.getActive().getSheets()[1].getDataRange().getValues();

function selectByIndex( index ) {
  Logger.log(table);
  for( var i in table )
    if( table[i][0] == index )
      return table[i];
  return null; //or throw exception
}

function mapValue(){
  //Get the key value sheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  Logger.log('on the loop');
  if (sheet != null) {

    for (var i in table){
      var groupKey =table[i][0];
      var groupValue = table[i][1];
      //Logger.log(groupKey+' '+groupValue);
      if(groupKey!=''&&groupValue!='')
        ScriptProperties.setProperty(groupKey,groupValue.toString());

      var assigneeKey = table[i][2];
      var assigneeValue = table[i][3];
      if(assigneeKey!=''&&assigneeValue!='')
        ScriptProperties.setProperty(assigneeKey,assigneeValue.toString());
    }
    //Priority
    ScriptProperties.setProperty('P: Low','1');
    ScriptProperties.setProperty('P: Normal','2');
    ScriptProperties.setProperty('P: High','3');
    ScriptProperties.setProperty('P: Urgent','4');
    //Browser.msgBox(ScriptProperties.getProperty('Development'));
  }
  else
  {
    Browser.msgBox('There is no sheet contain id for the assignee and group.');
  }
  //Browser.msgBox(ScriptProperties.getProperty('P: Urgent'));
}
function createPayLoad(count){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var requester = doc.getSheets()[0].getRange('a'+count).getValue();
  var ticketTitle = doc.getSheets()[0].getRange('b'+count).getValue().toString().replace('&','&#38;');
  var ticketComment = doc.getSheets()[0].getRange('c'+count).getValue().toString().replace('&','&#38;');
  ticketComment = replaceBR(ticketComment);
  ticketComment = replaceA(ticketComment);
  var assignee = ScriptProperties.getProperty(doc.getSheets()[0].getRange('d'+count).getValue());
  var group = ScriptProperties.getProperty(doc.getSheets()[0].getRange('e'+count).getValue());
  Logger.log(doc.getSheets()[0].getRange('f'+count).getValue());
  var priority = ScriptProperties.getProperty('P: '+doc.getSheets()[0].getRange('f'+count).getValue().trim());
  var tags = 'internal '+doc.getSheets()[0].getRange('g'+count).getValue();

        var payLoad  =  '\n';
        payLoad +=  '<ticket>'+'\n';
    payLoad +=  '<requester-email>'+requester+'</requester-email>'+'\n';
    payLoad +=  '<subject>'+ticketTitle+'</subject>'+'\n';
    payLoad +=  '<description>'+ticketComment+'</description>'+'\n';
    payLoad +=  '<assignee-id>'+assignee+'</assignee-id>'+'\n';
    payLoad +=  '<group-id>'+group+'</group-id>'+'\n';
    payLoad +=  '<priority-id>'+priority+'</priority-id>'+'\n';
    payLoad +=  '<set-tags>'+tags+'</set-tags>'+'\n';
    payLoad +=  '</ticket>';
  //Browser.msgBox(payLoad) 
  return payLoad;
}

function findThelastRow(column,index){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = doc.getLastRow();
  for(var i=2;i<=lastRow+1;i++)
  {
       if(doc.getSheets()[index].getRange(column+i).getValue()=="")
       {
         return i;
         break;
       }
  }
  return 0;
}
function authHandler(e){
  if(e.parameter.username=="")
  {
    Browser.msgBox('Username is required!');
    return;
  }
  var app = UiApp.getActiveApplication();

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var currentIndex = doc.getActiveSelection().getRowIndex();
  var username = e.parameter.username;
  var encode = Utilities.base64Encode(username+'/token:SECRET);
  var totalRow = findThelastRow('a',0);


  for (var count=2;count<totalRow;count++)
  {
    if(doc.getSheets()[0].getRange('a'+count).getValue()=='')
    {
       Browser.msgBox('Requester is required!');
       return;
    }
    var isCreate =  doc.getSheets()[0].getRange('i'+count).getValue();
    var isNormalUpdate = false;
    if(isCreate=='')
    {
      var payLoad = createPayLoad(count);

      var options =
          {
            "method" : "post",
            "headers" : {"Content-type":"application/xml","Authorization":  "Basic "+encode},
            "payload" : payLoad
          };

      var result = UrlFetchApp.fetch("http://SECRET/tickets.xml",options);

      if(result.getResponseCode()==201) //This means success.
      {
        var mytool_array=result.getHeaders().toSource().split(",");
        Logger.log(mytool_array);
        //var ticketNumber=mytool_array[2].split("Location:");
         var ticketNumber=result.getHeaders().toSource().split("Location:");
        Logger.log(ticketNumber);
        var valueTicket=ticketNumber[1].split('.xml')[0].split('tickets/')[1];
        doc.getSheets()[0].getRange('h'+count).setValue('=hyperlink("http://SECRET/tickets/'+valueTicket+'";"'+valueTicket+'")');
        doc.getSheets()[0].getRange('h'+count).setBackgroundColor('#1BE039');
        doc.getSheets()[0].getRange('i'+count).setValue('OK');
        doc.getSheets()[0].getRange('i'+count).setBackgroundColor('#1BE039');
      }
      else
      {
        doc.getSheets()[0].getRange('i'+count).setValue('FAIL');
        break;
        return;
      }
    }
  }
  // Clean up - get the UiApp object, close it, and return
  app.close();
  // The following line is REQUIRED for the widget to actually close.
  return app;
}

function continueSentTheRest(e){
  var app = UiApp.getActiveApplication();
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var indexOfA = findThelastRow('h',0);


  if(doc.getSheets()[0].getRange('a'+indexOfA).getValue()=="")
  {
    Browser.msgBox('Requester is required!');
    doc.getSheets()[0].setActiveSelection('a'+indexOfA);
    return;
  }


  var username = e.parameter.username;
  username += ':'+e.parameter.password;
  var encode = Utilities.base64Encode(username);
  var totalRow = findThelastRow('a',0);

  for (var count=indexOfA;count<totalRow;count++)
  {
    var payLoad = createPayLoad(count);

    var options =
        {
          "method" : "post",
          "headers" : {"Content-type":"application/xml","Authorization":  "Basic "+encode},
          "payload" : payLoad
        };

    var result = UrlFetchApp.fetch("http://SECRET/tickets.xml",options);

    if(result.getResponseCode()==201) //This means success.
    {
      var mytool_array=result.getHeaders().toSource().split(",");
      var ticketNumber=mytool_array[2].split("Location:");
      var valueTicket=ticketNumber[1].split('.xml')[0].split('tickets/')[1];
      doc.getSheets()[0].getRange('h'+count).setValue('=hyperlink("http://SECRET/tickets/'+valueTicket+'";"'+valueTicket+'")');
      doc.getSheets()[0].getRange('h'+count).setBackgroundColor('#1BE039');
      doc.getSheets()[0].getRange('i'+count).setValue('OK');
      doc.getSheets()[0].getRange('i'+count).setBackgroundColor('#1BE039');
    }
    else
    {
      doc.getSheets()[0].getRange('i'+count).setValue('FAIL');
      break;
      return;
    }
  }
  // Clean up - get the UiApp object, close it, and return
  app.close();
  // The following line is REQUIRED for the widget to actually close.
  return app;
}


String.prototype.ReplaceAll = function(stringToFind,stringToReplace){
    var temp = this;
    var index = temp.indexOf(stringToFind);
        while(index != -1){
            temp = temp.replace(stringToFind,stringToReplace);
            index = temp.indexOf(stringToFind);
        }
        return temp;
}

function replaceBR(input)
{
   return input.ReplaceAll('<br>','&#xD;');
}

function replaceA(input)
{
  input = input.ReplaceAll('<a>',' ');
  input = input.ReplaceAll('</a>',' ');
  return input;
}

function clearA(input)
{
  return input.replace(/<a>.*<\/a>/g,'<a></a>');
}

Answer 1

您原来的 findThelastRow(column,index) 函数有一个从事件工作表的 0 到 lastRow 的循环，但随后使用(最终)寻址其他工作表中的范围这个循环值。其他工作表中可能不存在该行索引。
多年来，您可能对此没有任何问题，具体取决于不同工作表中存在多少行，但也许现在这种情况正在发生......

为了防止这种风险，我建议在 findThelastRow 函数中尝试这样做:

function findThelastRow(column,index){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = doc.getSheets()[index].getLastRow();// check last row on the appropriate sheet
  for(var i=2;i<=lastRow+1;i++)
  {
       if(doc.getSheets()[index].getRange(column+i).getValue()=="")
       {
         return i;
         break;
       }
  }
  return 0;
}

编辑:这个函数可以是 written more efficiently不在相当慢的循环中使用 getValue() ...这是一个使用数组的兼容版本:(你可以替换它而不做任何改变)

function findThelastRow(column,index){
          var doc = SpreadsheetApp.getActiveSpreadsheet();
          var lastRow = doc.getSheets()[index].getLastRow()+1;// check last row on the appropriate sheet
          var coldata = doc.getSheets()[index].getRange(column+2+':'+column+lastRow).getValues();// I begin on Row 2 just as you did
      for(i=coldata.length-1;i>=0;i--){
         if(coldata[i][0]!=''){return i+2;break}
      }
      return 0;
    }