时间戳

首页 > 解决方案 > 电子表格无法运行脚本

javascript - 电子表格无法运行脚本

问题描述

我有一个运行脚本的电子表格，该脚本运行良好，但运行几次后出现错误。

有时错误 500，有时 400

>     Message Details
>     Request failed for https://docs.google.com returned code 500. Truncated server response: <! DOCTYPE html> <html lang = "en"> <head>
> <meta name = "description" content = "Word Processor, Presentations,
> and Web Spreadsheets"> <meta name = "viewport ... (use the
> muteHttpExceptions option to examine the full answer) (line 30,
> "Payments" file)

遵循电子表格，以便他们可以检查它以及其中的脚本是否有错误！

电子表格

有错误的脚本：

function Enviar_email_pdf() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    var url = ss.getUrl();
    url = url.replace(/edit$/,'');
    var url_ext = 'export?exportFormat=pdf&format=pdf' + 

    '&size=A4' + //paper size
    '&portrait=true' + //orientation, false for landscape
    '&fitw=true' + //fit to width, false for actual size
    '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
    '&top_margin=0.30'+
    '&bottom_margin=0.30'+
    '&left_margin=0.30'+
    '&right_margin=0.30'+
    '&horizontal_alignment=LEFT'+
    '&vertical_alignment=TOP'+
    '&gridlines=true' + //false = hide gridlines
    '&fzr=true' + //do not repeat row headers (frozen rows) on each page
    '&gid='; //leave ID empty for now, this will be populated in the FOR loop

  var token = ScriptApp.getOAuthToken();
  var sheets = ss.getSheets(); 
  var blobs = [];

  for (var i=0; i<6; i++) {  

    {muteHttpExceptions: true};
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {

      headers: {
        'Authorization': 'Bearer ' +  token
     }
   });  

  var linhaINICIAL = 2; 
  var colunaENVIO = 21;  
  var colunaSTATUS = 22;
  var textoCONDICAO = "ENVIAR";
  var textoENVIADO = "ENVIADO"
  var blobs = [];

   blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
        var array_blob = response.getBlob().setName(sheets[i].getName() + '.pdf');

  var aba = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BDPG"); 
  var intervalo = aba.getRange(linhaINICIAL,1,aba.getLastRow()-linhaINICIAL+1,colunaSTATUS);
  var dados = intervalo.getValues();
  var enviou = false;
  var email,assunto,mensagem;

  for (var i=0; i<dados.length; ++i) {
    if((dados[i][colunaENVIO-1]==textoCONDICAO) && (dados[i][colunaSTATUS-1]!=textoENVIADO)){


    var email    = dados[i][18];

    assunto = dados[i][19];

    var mensagem =   "Segue anexo relatório do período "+dados[i][2]+"!<br/><br/>"+

                     "Atenciosamente,"+"<br/><br/>"+


                  '<table  data-mysignature-date="2019-11-24T18:42:57.743Z" data-mysignature-is-paid="0" cellspacing="0" width="500" cellpadding="0" border="0"> <tr>  <td valign="top" style="vertical-align: top;" width="100"> <img alt="" width="100" style="width:100px;moz-border-radius:10%;khtml-border-radius:10%;o-border-radius:10%;webkit-border-radius:10%;ms-border-radius:10%;border-radius:10%;" src="https://img.mysignature.io/p/1/d/d/1dde7f1b-3d71-58dc-9e1e-34deb3dddcfa.png?time=1574620979" /> </td>  <td style="font-size:1em;padding: 0 0 0 15px;vertical-align: top;" valign="top"> <table cellspacing="0" cellpadding="0" border="0" style="width: 100%;max-width: 400px;line-height: 1.4;font-family:Trebuchet MS, Helvetica, sans-serif;font-size:110%;color: #000001;"> <tr> <td style="padding-bottom: 4px;"> <div style="font-family:Trebuchet MS, Helvetica, sans-serif;"> <span style="font-size:1.2em;color:#766f57;font-weight: 600;">Sidney Cardoso dos Santos</span>  </div> </td> </tr>    <tr> <td style="padding: 1px 0;"> <div style="font-family:Trebuchet MS, Helvetica, sans-serif;"> | <span style="font-weight: 600;color:#766f57;">phone:&nbsp;</span> <a style="color:#000001;" href="tel:(47) 99707-8273">(47) 99707-8273</a> </div> </td> </tr>   <tr> <td style="padding: 1px 0;"> <div style="font-family:Trebuchet MS, Helvetica, sans-serif;"> | <span style="font-weight: 600;color:#766f57;">email:&nbsp;</span> <a href="mailto:yendiscardoso@gmail.com" style="color:#000001;" target="_blank">yendiscardoso@gmail.com</a> </div> </td> </tr>     <tr> <td style="padding: 1px 0;"> <div style="font-family:Trebuchet MS, Helvetica, sans-serif;"> | <span style="font-weight: 600;color:#766f57;">address:&nbsp;</span>Rua Clézio Roberto Silveira - Joinville - SC </div> </td> </tr>    <tr> <td style="color: #000001;padding: 8px 0 3px 0;display: block;"> <div style="font-family:Trebuchet MS, Helvetica, sans-serif;">  </div> </td> </tr> <tr> <td> <table cellspacing="0" cellpadding="0" border="0"> <tr> <td style="font-family: Arial;padding: 0 4px 0 0;"><a href="https://www.youtube.com/channel/UCrLigXL1tVUJyvzNM4Gt-PQ"><img alt="" style="width:21px;" width="21" src="https://img.mysignature.io/s/v3/d/2/7/d276dfc5-e0f2-5449-b7a3-0a2c9cbf1ed2.png"></a></td><td style="font-family: Arial;padding: 0 4px 0 0;"><a href="https://www.linkedin.com/in/sidneycardosodossantos/"><img alt="" style="width:21px;" width="21" src="https://img.mysignature.io/s/v3/c/b/d/cbd01dd8-9e27-5cde-9822-51483a5e8639.png"></a></td><td style="font-family: Arial;padding: 0 4px 0 0;"><a href="https://support.google.com/s/community/forum/1386689/user/1784968"><img alt="" style="width:21px;" width="21" src="https://img.mysignature.io/s/v3/1/1/f/11fc1ed6-c858-55c7-9963-768e9f7d95dc.png"></a></td> </tr> </table> </td> </tr>  </table> </td> </tr></table><table style="color: gray;font-family: Arial;line-height: 1.3;font-size:1em;width: 100%" cellspacing="0" cellpadding="0" border="0"> <tr> <td style="font-size:85%;padding-top: 15px;"> IMPORTANT: The contents of this email and any attachments are confidential. It is strictly forbidden to share any part of this message with any third party, without a written consent of the sender. If you received this message by mistake, please reply to this message and follow with its deletion, so that we can ensure such a mistake does not occur in the future. </td> </tr></table><table style="color: gray;font-family: Arial;line-height: 1.3;font-size:1em;width: 100%" cellspacing="0" cellpadding="0" border="0"> <tr> <td style="font-size:85%;padding-top: 15px;"> Please do not print this email unless it is necessary. Every unprinted email helps the environment. </td> </tr></table><table class="branding" cellspacing="0" cellpadding="0" border="0"> <tr><td style="display:block;padding:15px 0 0 0;"></td></tr> <tr> <td style="border-top: 1px solid #eeeeee;padding-top: 5px;font-size:10px;font-family: Arial;"> <a href="https://mysignature.io?utm_source&#x3D;promotion&amp;utm_medium&#x3D;signature&amp;utm_campaign&#x3D;create_own_signature" style="color: #000001; text-decoration: none" target="_blank">'

    MailApp.sendEmail(email, assunto, mensagem,{htmlBody:mensagem,attachments:[array_blob]});

    aba.getRange(linhaINICIAL+i,colunaSTATUS).setValue(textoENVIADO);
      enviou = true;
      SpreadsheetApp.flush();

    }
   } 
}
}

错误显示在第 30 行

   var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {

我是脚本初学者，我懂的很少，我执行得最多的代码都在这里！

我为英国人道歉，因为他们来自巴西，只知道基础知识。

如果有人可以帮助我，谢谢！

标签： javascriptgoogle-apps-scriptgoogle-sheetsurlfetch

解决方案

推荐阅读