javascript - 将VBA宏转换为Google表格

您可以尝试以下代码：

function exportSingleSheetToPdf() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dispatch1 = ss.getSheetByName("Dispatch1");
  var invoiceblank = ss.getSheetByName("invoiceblank");
  var customers = ss.getSheetByName("Customers");

  var lastrow = getLastRowSpecial(ss.getRange("O:O").getValues());

  for (var r = 5;r<11;r++) {
    if (dispatch1.getRange(r, 1).getValue() == "done") {continue;}
    var invoicedate = dispatch1.getRange(r, 5).getValue();
    var rate = dispatch1.getRange(r, 15).getValue();
    var invoicenumber = dispatch1.getRange(r, 17).getValue();
    var amount = dispatch1.getRange(r, 19).getValue();

    invoiceblank.getRange("F4").setValue(invoicedate);
    invoiceblank.getRange("G4").setValue(invoicenumber);
    invoiceblank.getRange("F18").setValue(rate);

    var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
    var projectname = SpreadsheetApp.getActiveSpreadsheet();
    invoiceblank.copyTo(newSpreadsheet);

    newSpreadsheet.getSheetByName('Sheet1').activate();
    newSpreadsheet.deleteActiveSheet();

    var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();

    var folder = DriveApp.getFolderById(""); // Fill in the id of the folder in drive you want your pdf to be saved in.
    var fileName = invoiceblank.getRange("G4").getValue() + customers.getRange("A1").getValue() + invoiceblank.getRange("A16").getValue() + ".pdf";
    var file = folder.createFile(fileName, pdf);

    DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
  }
}

function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
}

由于这是Google表格，因此您的pdf将保存到您的Google驱动器。

确保填写要在其中存储文件的文件夹的文件夹ID。在代码中应在此位置指示该ID。

祝好运 ：）