-
-
Save mikestarks91/88d45370b814c1ff90f0 to your computer and use it in GitHub Desktop.
Batch import CSV to Spreadsheet and Export PDF
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function main(){ | |
var counter = 5; | |
var blobs = []; | |
var zipBlob = null; | |
var files = DriveApp.getFolderById(folder_id).getFilesByType("text/csv"); | |
var csver = new CSVer(); | |
csver.setUpSpreadsheet(); | |
while(files.hasNext()){ | |
var file = files.next(); | |
csver.importFile(file); | |
blobs.push(csver.toPDF(file)); | |
// With personal, non-business google accounts, you get a "429" error here, or "Too Many Requests". I | |
// used Utilites.sleep() to try to lower the rate of requests to the appropriate threshold. | |
// Slows down the whole process quite a bit, plus it makes it more likely to timeout with higher file volumes. | |
Utilities.sleep(10000); | |
csver.clearSheet(); | |
} | |
// add files to zip folder | |
zipBlob = Utilities.zip(blobs).setName(zipName + '.zip'); | |
DriveApp.createFile(zipBlob); | |
MailApp.sendEmail(emailAddress, "Error Log", Logger.getLog()); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var CSVer = function() { | |
var spreadsheet_ = null; | |
var dataSheet = null; | |
// Set spreadsheet variable and creates new sheet within CSVer() object | |
this.setUpSpreadsheet = function(){ | |
spreadsheet_ = SpreadsheetApp.openById(ss_id); | |
dataSheet = spreadsheet_.getSheetByName(dataTab_name); | |
return this; | |
} | |
// Import file data into spreadsheet | |
// WARNING: The CSV parser really doesn't like values with (") or ('). If these characters are | |
// included in your values, it will most likely skip the file that contains them | |
this.importFile = function(file){ | |
try{ | |
var csv = file.getBlob().getDataAsString().split('\n'); | |
var len = csv[0].parseCSVtoArray().length; | |
var data = []; | |
for(var i = 0; i < csv.length; i++){ | |
var row = csv[i].parseCSVtoArray(); | |
if(row.length == len) | |
data.push(row); | |
} | |
// .getDisplayValues appears to force the function to wait until cells are filled. This doesn't work without it. | |
dataSheet.getRange(1, 1, data.length, data[0].length).setValues(data).getDisplayValues(); | |
SpreadsheetApp.flush(); | |
return dataSheet; | |
}catch(err){ | |
Logger.log(JSON.stringify(err)); | |
return null; | |
} | |
} | |
// Create pdf from report template | |
// Credit goes to Amit Agarwal @ https://ctrlq.org/code/19869-email-google-spreadsheets-pdf | |
this.toPDF = function(file){ | |
// Get tab you want to export as pdf | |
var ss = spreadsheet_; | |
var report = ss.getSheetByName(reportTab_name); | |
// set up url query | |
var url = ss.getUrl(); | |
url = url.replace(/edit$/,''); | |
/* Specify PDF export parameters | |
// From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579 | |
exportFormat = pdf / csv / xls / xlsx | |
gridlines = true / false | |
printtitle = true (1) / false (0) | |
size = legal / letter/ A4 | |
fzr (repeat frozen rows) = true / false | |
portrait = true (1) / false (0) | |
fitw (fit to page width) = true (1) / false (0) | |
add gid if to export a particular sheet - 0, 1, 2,.. | |
*/ | |
var url_ext = 'export?exportFormat=pdf&format=pdf' // export as pdf | |
+ '&size=letter' // paper size | |
+ '&portrait=false' // orientation, false for landscape | |
+ '&fitw=true&source=labnol' // fit to width, false for actual size | |
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers | |
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines | |
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page | |
+ '&gid='; // the sheet's Id | |
var token = ScriptApp.getOAuthToken(); | |
// Convert worksheet to PDF | |
var pdf = UrlFetchApp.fetch(url + url_ext + report.getSheetId(), { | |
headers: { | |
'Authorization': 'Bearer ' + token | |
} | |
}); | |
//convert the response to a blob and return it. Sets name to name of .csv | |
var fileName = file.getName(); | |
fileName = fileName.split(''); | |
// Cut .csv extension out of file name | |
fileName.splice(-4,4); | |
fileName = fileName.join(''); | |
return pdf.getBlob().setName(fileName + '.pdf'); | |
} | |
// Clears DataPile sheet | |
this.clearSheet = function() { | |
dataSheet.clear(); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Declare ID of Google Drive Folder you will be pulling CSVs from | |
var folder_id = "<FOLDER_ID>"; | |
// Declare ID of Spreadsheet where you want to import CSV data | |
var ss_id = "<SPREADSHEET_ID>"; | |
// Declare name of Tab where you want to spit CSV data | |
var dataTab_name = "<TAB_NAME>"; | |
// Declare name of Tab you want to export as a PDF report | |
var reportTab_name ="<TAB_NAME>"; | |
// Declare name of .zip file where your reports will be added | |
var zipName = "<ZIPFILE_NAME>"; | |
// Set email address for error logger | |
var emailAddress = "<EMAIL_ADDRESS>"; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
String.prototype.parseCSVtoArray = function() { | |
var text = this; | |
var re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*)*$/; | |
var re_value = /(?!\s*$)\s*(?:'([^'\\]*(?:\\[\S\s][^'\\]*)*)'|"([^"\\]*(?:\\[\S\s][^"\\]*)*)"|([^,'"\s\\]*(?:\s+[^,'"\s\\]+)*))\s*(?:,|$)/g; | |
if (!re_valid.test(text)) return null; | |
var a = []; | |
text.replace(re_value, | |
function(m0, m1, m2, m3) { | |
if (m1 !== undefined) a.push(m1.replace(/\\'/g, "'")); | |
else if (m2 !== undefined) a.push(m2.replace(/\\"/g, '"')); | |
else if (m3 !== undefined) a.push(m3); | |
return ''; | |
}); | |
if (/,\s*$/.test(text)) a.push(''); | |
return a; | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment