从Google表格发送活动表格作为电子邮件附件 [英] Send the active sheet as an email attachment from google sheets

问题描述

我有一个Google表单，可以捕获电子表格中的回复.当前，每次做出新的响应时，它都会创建一个新的工作表.

I have a google form that captures responses in a spreadsheet. It currently creates a new sheet everytime a new response is made.

我现在正尝试向现有的创建新工作表的脚本中添加通过邮件发送活动工作表脚本".

I'm now trying to add a "mail the active sheet script" to the existing script that creates a new sheet.

但是我收到一条错误消息:请求 https://docs.google. com/spreadsheets/d/SS_ID/export ?"围绕以下

However I get an error "Request failed for https://docs.google.com/spreadsheets/d/SS_ID/export?" around the following

var result = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

我已将本文用作发送的指南电子邮件部分.

I have used this article as a guide for the send email part.

这是我正在使用的整个脚本.

This is the entire script I am using.

    function onSubmit(e){
  Logger.log('submit ran');

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Get last row of data
  var lastRow = sheet.getLastRow();
  var colB_Data = sheet.getRange(lastRow, 2).getValue();

  //create sheet name with order no.
  ss.insertSheet(colB_Data);

  //order formula
  var sheets = ss.getSheets()[1];

  var cell = sheets.getRange("b2");
  cell.setFormula("=TRANSPOSE('Form Responses 1'!B1:AR1)");

  var cell = sheets.getRange("c2");
  cell.setFormula("=sheetName()");

  var cell = sheets.getRange("c3");
  cell.setFormula("=transpose(FILTER('Form Responses 1'!C:AR,'Form Responses 1'!B:B=C2))");

  var cell = sheets.getRange("d5:d44");
  cell.setFormula("=C5*vlookup(B5,'RG Cost'!B:E,4,0)");

  var cell = sheets.getRange("d5:d44");
  cell.setNumberFormat("[$₹][>9999999]##\,##\,##\,##0;[$₹][>99999]##\,##\,##0;[$₹]##,##0")

  var cell = sheets.getRange("c47");
  cell.setFormula("=sum(C5:C44)");

  var cell = sheets.getRange("d47");
  cell.setFormula("=sum(D5:D44)");

  var cell = sheets.getRange("b47");
  cell.setValue('TOTAL');


  //Send active sheet as email attachment

  var ssID = SpreadsheetApp.getActiveSpreadsheet();
  var email = Session.getUser().getEmail();
  var subject = "Order no.";
  var body = "Hello";

  var token = ScriptApp.getOAuthToken();


  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?" + "format=xlsx" +  "&gid=" + "&portrait=true" + "&exportFormat=xlsx";

  var result = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });


  var contents = result.getContent();

  MailApp.sendEmail("[e-mail]",subject ,body, {attachments:[{fileName:colB_Data+".pdf", content:contents, mimeType:"application//pdf"}]}); 

};

推荐答案

正如@cooper之前提到的那样，未将ssID正确添加到URL，并且还要对url和fetchapp进行一些更改

As mentioned by @cooper before, the ssID wasn't properly added to the URL, and also there's a few changes to be made the url and your fetchapp

  //Send active sheet as email attachment

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetgId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
  var email = Session.getUser().getEmail();
  var subject = "Order no.";
  var body = "Hello";


  var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export?" + "format=xlsx" +  "&gid="+sheetgId+ "&portrait=true" + "&exportFormat=pdf";


  var result = UrlFetchApp.fetch(url)

  var contents = result.getContent();

  MailApp.sendEmail("[e-mail]",subject ,body, {attachments:[{fileName:colB_Data+".pdf", content:contents, mimeType:"application//pdf"}]});

您是直接将HTTP GET发送到同一驱动器，因此我认为该用户已登录到google帐户并可以访问电子表格，因此您实际上不需要再次使用令牌. 其次，您忘记将变量添加到导出模式URL中的参数中，而我将导出格式更改为pdf.

You're sending an HTTP GET directly to your same drive, so the user, I assume, is logged to a google account and has access to the spreadsheet, therefore you don't really need a token again. Second you forgot to add the variables to the parameters in the export mode URL and I changed the export format to pdf.

这篇关于从Google表格发送活动表格作为电子邮件附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！