Set multiple attachment names same as cell values

Question

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);

Answer 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