How to create a CSV file from a selected range in Google Sheets?

Question

We are migrating our Information Systems from Excel to Google Sheets, and an important part of the process is the functionality to create multiple CSV files based on multiple different ranges (for now can just be given as static range e.g. "B8:K500"), each CSV file titled the respective currency. We use this to send out Payroll, which we then upload to PayPal which pays out with needed information being ( [Email], [Currency], [Dollar Amount] {carriage return for next employee})

We had this functionality already setup in VBA, as shown at the bottom, but I am not proficient in Javascript, much less Google Apps Script and I am not sure where to start.

I found some example code that is almost there but when I run it it shows error "Specified range must be part of the sheet. (line 5, file "Csv New")" and after debugging, it shows (folder, i, sheet, and csvFile) variables as undefined with (range) listed as (Object (1951973745)). This code can be found here:

function ExportCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var range = ss.getSheets()[0].getRange("B8:K500");
   SpreadsheetApp.setActiveRange(range);

  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
  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
    folder.createFile(fileName, csvFile);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}


function convertRangeToCsvFile_(csvFileName, sheet) {  
   // get available data range in the spreadsheet
  var range = sheet.getRange("B8:K500"); 
  var values = SpreadsheetApp.getActiveSheet().getRange("B8:K500").getValues();

  var csvFile = values;
  DriveApp.createFile("mycsv.csv", csvFile);

  }

Could this be a permissions issue related to if it's published or not? I've found similar problems in StackOverflow like:

Google Script Cannot call FormApp.getUi() from this context

How to export google sheet as CSV by selected columns

but they don't hit directly fix the problem experienced here.

Let me know if I can provide any other info!

Thanks :)

Associated VBA code:

Sub WritePaypal(ByVal thisRange As Range, ByVal filePath As String, Optional ByVal fileAppend As Boolean = False)
    Dim cLoop As Long, rLoop As Long
    Dim ff As Long, strRow As String

    ff = FreeFile
    If fileAppend Then
        Open filePath For Append As #ff
    Else
        Open filePath For Output As #ff
    End If

    For rLoop = 1 To thisRange.Rows.Count
        strRow = ""
        For cLoop = 1 To thisRange.Columns.Count
            If cLoop > 1 Then strRow = strRow & vbTab
            strRow = strRow & thisRange.Cells(rLoop, cLoop).Value
        Next                                     'cLoop
        Print #ff, strRow
    Next                                         'rLoop

    Close #ff

    Range("A1").Activate
    MsgBox "Done"
End Sub

Sub WriteFile(ByVal curr As String, ByVal rng As Range)
    Dim myPath As String
    Dim filePath As String
    Dim myrng As Range
    Dim Cell As Range

    'Initialize
    myPath = ""


    ' User chooses path to save .txt file to
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Location to Save File to"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & Application.PathSeparator
    End With


    'In Case of Cancel
NextCode:
    If myPath = "" Then Exit Sub

    filePath = myPath & curr & ".txt"

    'To Test
    'MsgBox myPath
    'MsgBox filePath


    On Error Resume Next
    Set myrng = rng

    If myrng Is Nothing Then
        MsgBox "No cells selected"
        Exit Sub
    Else
        WritePaypal myrng, filePath, False
    End If

    ScreenUpdating = True


End Sub

Sub WriteUSD()
    Call WriteFile("USD", Range("Z5:AB26"))
End Sub

Sub WriteAUD()
    Call WriteFile("AUD", Range("Z30:AB32"))
End Sub

Sub WriteGBP()
    Call WriteFile("GBP", Range("Z35:AB35"))
End Sub

Answer 1

You should use getActiveRangeList(), which will allow you to get the multiple selections made by the user. From those ranges, you can get the values, and then compile a CSV.

I've attached sample code below, which shows you in a very rudimentary way how to go about doing this. Please note that it does not handle cases where selections are of different sizes (e.g. you selected 5 emails but 20 dollar amounts). The sample code also does not include your existing features for folder creation/naming or the message box.

function createCsvFromSelections() {
  var activeRanges = SpreadsheetApp.getActiveRangeList().getRanges();
  var mergedData = [];

  // Loop through the ranges, rows, and columns in that order so that the
  // values can be recorded in the correct order.
  for (var i in activeRanges) {
    var values = activeRanges[i].getValues(); // 2-d array, row[]col[]
    for (var rowIndex in values) {
      var row = values[rowIndex];
      if (!Array.isArray(mergedData[rowIndex])) // Check if a row was already created
          mergedData[rowIndex] = []; // Add a blank row to push the values into
      for (var colIndex in row) {
        mergedData[rowIndex].push(row[colIndex]);
      }
    }
  }

  // Convert mergedData array to CSV string
  var csvData = "";
  for (var rowIndex in mergedData) {
    csvData += (mergedData[rowIndex].join(",") + "\n");
  }

  // Create the file
  DriveApp.createFile("2019-05-15 Selections", csvData, MimeType.CSV);
}