csv - 如何从 Google 表格中的选定范围创建 CSV 文件？

Question

我们正在将我们的信息系统从 Excel 迁移到 Google 表格，该过程的一个重要部分是基于多个不同范围创建多个 CSV 文件的功能（现在只能作为静态范围给出，例如“B8:K500”），每个 CSV 文件都以各自的货币为标题。我们使用它来发送工资单，然后我们将其上传到 PayPal 支付所需的信息是（[电子邮件]、[货币]、[美元金额]{下一位员工的回车}）

我们已经在 VBA 中设置了这个功能，如底部所示，但我不精通 Javascript，更不用说 Google Apps 脚本，我不知道从哪里开始。

我发现了一些几乎存在的示例代码，但是当我运行它时，它显示错误“指定范围必须是工作表的一部分。（第 5 行，文件“Csv New”）”，调试后，它显示（文件夹，i，工作表, 和 csvFile) 变量未定义， (range) 列为 (Object (1951973745))。可以在此处找到此代码：

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);

  }

这可能是与是否发布有关的权限问题吗？我在 StackOverflow 中发现了类似的问题，例如：

Google 脚本无法从此上下文中调用 FormApp.getUi()

如何通过选定的列将谷歌工作表导出为 CSV

但他们并没有直接解决这里遇到的问题。

让我知道我是否可以提供任何其他信息！

谢谢 ：）

相关的 VBA 代码：

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

标签： csvgoogle-apps-scriptgoogle-sheetsexport-to-csv

Answer 1