Google Script on Attaching List of excel files from a Spreadsheet

Question

I two spreadsheet Main and AddFiles wherein Add file has Two columns Subject and Attachments where under attachments is the list of excel file names with like file1.xlsx and file2.xlsx. Where I uploaded this file in my Google Drive under the Report folder.

I used the code below but always got an error on the last execution. It doesn't recognize the .getAs(MimeType.xlsx)

var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange(2,1,1,1)
data = dataRange.getValues()
var e = data[0][0]
for (var i = 0; i < (e-1); i++) {
draftmail();
}
function draftmail(){

var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange(1,1,1,1)
var data = dataRange.getValues()
var msg = data[0][0]

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AddFiles");

var startRowx = 1
var numRowx = 1
var dataRangeTox = sheet.getRange(startRowx,3,numRowx,1)
var datax = dataRangeTox.getValues()
for (x in datax) {
var rowx = datax[x];
var to = rowx[0];

var Starta = 2 + i
var numRowa = 1
var dataRangeToa = sheet2.getRange(Starta,1,1,1)
var dataa = dataRangeToa.getValues()
for (a in dataa) {
var rowa = dataa[a];
var subject = rowa[0];


var Startb = 2 + i
var numRowb = 1
var dataRangeTob = sheet2.getRange(Startb,2,1,1)
var datab = dataRangeTob.getValues()
for (b in datab) {
var rowb = datab[b];
var datafile = rowb[0];
var file = DriveApp.getFilesByName(datafile)

var startRowy = 1
var numRowy = 1
var dataRangeToy = sheet.getRange(startRowy,4,numRowy,1)
var datay = dataRangeToy.getValues()
for (y in datay) {
var rowy = datay[y];
var carboncopy = rowy[0];

if (file.hasNext()){ 

GmailApp.createDraft(to,subject,msg,{ cc: carboncopy}, {
 attachments: [file[0].getAs(MimeType.xlsx)],
 })
}
}
}
}
}
}

Aaron Dunigan AtLee · Answer 1 · 2020-09-10T00:35:39.470

2

When you call

file = DriveApp.getFilesByName(datafile)

it returns a FileIterator collection, which is not indexed like an array (file[0]) but rather requires you to call file.next() to get the next file.

In addition, the MimeType Enum for an .xlsx file is MimeType.MICROSOFT_EXCEL.

So change

attachments: [file[0].getAs(MimeType.xlsx)]

to this instead:

attachments: [file.next().getAs(MimeType.MICROSOFT_EXCEL)]

edited Sep 10 '20 at 00:35

answered Sep 10 '20 at 00:29

Aaron Dunigan AtLee

1,860
7
18

I get an Error. Exception: The parameters (String,String,String,(class),(class)) don't match the method signature for GmailApp.createDraft. (line 57, file "draft") – WIL Sep 10 '20 at 00:53

Marios · Accepted Answer · 2020-09-10T01:27:08.057

Modification points:

Assuming that everything else works properly in your code, you should make the following changes:

cc and attachments should be passed as a single json object.
file[0] should be file.next().
MimeType.xlsx should be MimeType.MICROSOFT_EXCEL.

Solution:

GmailApp.createDraft(to,subject,msg,
   { cc: carboncopy,
     attachments: [file.next().getAs(MimeType.MICROSOFT_EXCEL)]
})

Google Script on Attaching List of excel files from a Spreadsheet

2 Answers2

Modification points:

Solution:

References: