将多个附件名称设置为与单元格数值相同。

huangapple go评论87阅读模式
英文:

Set multiple attachment names same as cell values

问题

问题

我发送了一封带有多个附件的电子邮件,但是邮件中却有两个附件具有相同的名称,尽管单元格I6的值不同(两个工作表:“HANG III,IV-Q.1”,“HANG II-Q.1”)。只有来自工作表“HANG II-Q.1”的单元格值的名称设置正确。

我想要将多个附件的名称设置为与单元格值相同。如何正确更改代码?

代码

function sendtwo() {
  var url = "https://docs.google.com/spreadsheets/d/" +
    SpreadsheetApp.getActiveSpreadsheet().getId() +
    "/export" +
    "?format=xlsx&" +
    "gid=" + 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HANG III,IV-Q.1").getSheetId();
    
  var url1 = "https://docs.google.com/spreadsheets/d/" +
    SpreadsheetApp.getActiveSpreadsheet().getId() +
    "/export" +
    "?format=xlsx&" +
    "gid=" +
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HANG II-Q.1").getSheetId();

  var urlA = [];
  
  [url, url1].forEach((url, i) => {
    var params = {
      method: "GET",
      headers: {
        authorization: `Bearer ${ScriptApp.getOAuthToken()}`
      }
    };
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets()
    var name = ss.getSheetByName("HANG III,IV-Q.1").getRange('I6').getValue()+ '.xlsx';
    var name = ss.getSheetByName("HANG II-Q.1").getRange('I6').getValue()+ '.xlsx';
    urlA.push(UrlFetchApp.fetch(url, params).getBlob().setName(name));
  });

  var date = Utilities.formatDate(new Date(), "GMT+7", "dd-MM-yyyy")
  var message = {
    to: "example@gmail.com",
    subject:`Example Subject ${date}`,
    body: "Lorem ipsum...",
    name: "Example Name",
    attachments: urlA
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Example Menu").addItem("Example Item", "sendtwo").addToUi();
}

MailApp.sendEmail(message);

如果您需要进一步的帮助或有其他问题,请随时提出。

英文:

Problem

I sent an email with more than one attachment, but the email was sent with two attachments having the same name, even though the cell values from I6 are different (two sheets: HANG III,IV-Q.1, HANG II-Q.1). Only the name of the cell value from the sheet HANG II-Q.1 was set correctly.

I want to set multiple attachment names to be the same as the cell values. How can I change the code correctly?

Code

function sendtwo() {
  var url = "https://docs.google.com/spreadsheets/d/" +
    SpreadsheetApp.getActiveSpreadsheet().getId() +
    "/export" +
    "?format=xlsx&" +
    "gid=" + 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HANG III,IV-Q.1").getSheetId();
    
  var url1 = "https://docs.google.com/spreadsheets/d/" +
    SpreadsheetApp.getActiveSpreadsheet().getId() +
    "/export" +
    "?format=xlsx&" +
    "gid=" +
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HANG II-Q.1").getSheetId();

  var urlA = [];
  
  [url, url1].forEach((url, i) => {
    var params = {
      method: "GET",
      headers: {
        authorization: `Bearer ${ScriptApp.getOAuthToken()}`
      }
    };
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets()
    var name = ss.getSheetByName("HANG III,IV-Q.1").getRange('I6').getValue()+ '.xlsx';
    var name = ss.getSheetByName("HANG II-Q.1").getRange('I6').getValue()+ '.xlsx';
    urlA.push(UrlFetchApp.fetch(url, params).getBlob().setName(name));
  });

  var date = Utilities.formatDate(new Date(), "GMT+7", "dd-MM-yyyy")
  var message = {
    to: "example@gmail.com",
    subject:`Example Subject ${date}`,
    body: "Lorem ipsum...",
    name: "Example Name",
    attachments: urlA
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Example Menu").addItem("Example Item", "sendtwo").addToUi();
}

MailApp.sendEmail(message);

答案1

得分: 0

您在使用相同的参数name来存储这两个名称。请分别使用相关单元格。我将发布使用Google脚本的传统工作方式,请参考以下内容并根据您的用例进行调整。

import { SpreadsheetApp } from '@dgcode/spreadsheet-app';
var recipient = 'recipient_email@example.com';
var subject = 'Sheets Attachments';
var body = 'Please find the attached sheets.';

// 获取活动电子表格和文档的工作表名称
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet("https://docs.google.com/spreadsheets/d/10KHGs2oA6I0YhKVplqQRo74hcOAGJuCZjNbrqecOFcQ/edit?usp=sharing");
var sheet1Name = "HANG III,IV- Q.1";
var sheet2Name = 'HANG II- Q.1';

// 单独获取工作表对象
var sheet1 = spreadsheet.getSheetByName(sheet1Name);
var sheet2 = spreadsheet.getSheetByName(sheet2Name);

// 将工作表转换为PDF
var sheet1Pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf');
var sheet2Pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf');

// 附加转换后的PDF及其名称
var attachments = [
  { fileName: sheet1Name + '.pdf', content: sheet1Pdf.getBytes() },
  { fileName: sheet2Name + '.pdf', content: sheet2Pdf.getBytes() }
];

// 将所有数据传递到此处并发送电子邮件
MailApp.sendEmail({
  to: recipient,
  subject: subject,
  body: body,
  attachments: attachments
});

> 请参考[此处的工作代码][1]
英文:

You are using same parameter name to store the both the names. Respectively use relevent cells as well. I will post the conventional way of working google script please refer the below and adopt accordingly to your use case.

import { SpreadsheetApp } from '@dgcode/spreadsheet-app';
var recipient = 'recipient_email@example.com';
var subject = 'Sheets Attachments';
var body = 'Please find the attached sheets.';
// Get active spreadsheet and sheet names of documents
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet("https://docs.google.com/spreadsheets/d/10KHGs2oA6I0YhKVplqQRo74hcOAGJuCZjNbrqecOFcQ/edit?usp=sharing");
var sheet1Name = "HANG III,IV- Q.1";
var sheet2Name = 'HANG II- Q.1';
// Get the sheet objects separately
var sheet1 = spreadsheet.getSheetByName(sheet1Name);
var sheet2 = spreadsheet.getSheetByName(sheet2Name);
// Converting sheets to PDF
var sheet1Pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf');
var sheet2Pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf');
// Attaching converted pdf with its name
var attachments = [
{ fileName: sheet1Name + '.pdf', content: sheet1Pdf.getBytes() },
{ fileName: sheet2Name + '.pdf', content: sheet2Pdf.getBytes() }
];
// Pass all data here and send email
MailApp.sendEmail({
to: recipient,
subject: subject,
body: body,
attachments: attachments
});

> Refer to the working code here

huangapple
  • 本文由 发表于 2023年7月3日 14:15:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76602235.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定