CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Export a single Google Sheet to CSV File

I have a sheet named "IMS Import" and I am trying to export the range A3:E to a CSV file by a Google Apps script which will be triggered by a button. The length of the range to be exported changes from week to week so I only want to capture the last populated row. I have the following script but I get a Google error "403. That’s an error. We're sorry, but you do not have access to this page. That’s all we know.":

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Download IMS Import File", functionName: "saveAsCSV"}];
  ss.addMenu("Creating a Timetable", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('IMS Import');
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
  // 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 file = folder.createFile(fileName, csvFile);
  //File downlaod
  var downloadURL = file.getDownloadUrl().slice(0, -8);
  showurl(downloadURL);
  
}

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    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 = 2; 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);
  }
}

I get the error at this point in the script: 

SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');

I am hoping someone can help me to get a functioning script so that it creates a CSV from A3 to the last populated row.

Upvotes: 0

Views: 330

Answers (1)

Cooper
Cooper

Reputation: 64032

If you only want the last row of the range change this

var activeRange = sheet.getDataRange();

to this:

var activeRange = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn());

As far as page access goes I can't be sure. Perhaps you're logged into to two different accounts.

Upvotes: 2

Related Questions