0

Wondering if anyone can assist in debugging this Google app script. The script works as needed to create CSV files of each Sheet in my workbook, but it throws an error every time:

"TypeError: Cannot call method "getSheets" of null. (line 10, file "Code")"

I run this on a timer trigger every 15 minutes, so I'm starting to get a lot of error messages piling up, even though the output is correct each time.

//DOWNLOAD CSVs
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}];
ss.addMenu("csv", csvMenuEntries);
};

function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
// DriveApp.
// create a folder from the name of the spreadsheet
//var folder = DriveApp.getFoldersByName("BrandonTestFolder");
for (var i = 0 ; i < sheets.length ; i++) {
var sheet = sheets[i];
// append ".csv" extension to the sheet name
fileName = sheet.getName() + ".csv";
// convert all available sheet data to csv format
var csvFile = convertRangeToCsvFile_(fileName, sheet);
// create a file in the Docs List with the given name and the csv data
var files = DriveApp.getFilesByName(fileName);
while(files.hasNext()) {
var file = files.next();
file.setContent(csvFile)
}
//DriveApp.createFile(fileName, csvFile)
}
// Browser.msgBox('Files are waiting in a folder named ' + DriveApp.getRootFolder().getName());
}
function convertRangeToCsvFile_(csvFileName, sheet) {
// get available data range in the spreadsheet
var activeRange = sheet.getDataRange();
try {
var data = activeRange.getDisplayValues();
var csvFile = undefined;
// loop through the data in the range and build a string with the csv data
if (data.length > 1) {
var csv = "";
for (var row = 0; row < data.length; row++) {
  for (var col = 0; col < data[row].length; col++) {
if (data[row][col].toString().indexOf(",") != -1) {
data[row][col] = "\"" + data[row][col] + "\"";
}
}
// join each row's columns
// add a carriage return to end of each row, except for the last one
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}

catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
  • 1
    Could someone explain why this would be better off on Stack Overflow? It does involve Google Sheets, which would seem to make it on-topic here. – jonsca Apr 26 '18 at 22:44
  • For those who vote to migrate the question, bear in mind that scope overlapping is allowed between sites of the SE network so a question could be on topic on several sites by the other hand google-apps-script is on the top 10 on tags by popularity rank on this site. – Rubén Apr 27 '18 at 3:51
1

The "active" methods, like getActiveSpreadsheet(), return null when the script is triggered by a time-driven trigger. You should replace this by openById('yourSpreadsheetId')/openByUrl('yourSpreadsheetUrl') methods.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.