Warm tip: This article is reproduced from stackoverflow.com, please click
excel google-apps-script google-sheets javascript vba

Convert VBA macro to Google sheets

发布于 2020-04-05 00:22:00

I need a way to translate a VBA Excel macro to Google sheets, the code below transfers a list of data to an invoice and then saves it as a PDF. I searched online for a translator but didn't find it. If some one knows one or if some one can translate this one.

Private Sub CommandButton1_Click()

    'define value
    Dim customer As String
    Dim invoicenumber As Long
    Dim invoicedate As Long
    Dim path As String
    Dim myfilename As String
    Dim rate As Long
    Dim r As Long

    'define our last row
    lastrow = Sheets("Dispatch1").Range("O" & Rows.Count).End(xlUp).Row

    ' start at row 5
    r = 5
    For r = 5 To 10
        If Cells(r, 1).Value = "done" Then GoTo nextrow
        invoicedate = Sheets("Dispatch1").Cells(r, 5).Value
        rate = Sheets("Dispatch1").Cells(r, 15).Value
        invoicenumber = Sheets("Dispatch1").Cells(r, 17).Value
        Amount = Sheets("Dispatch1").Cells(r, 19).Value
        Application.DisplayAlerts = False
        Sheets("invoiceblank").Select

        ' map the variables to invoice worksheet data
        ActiveSheet.Range("F4").Value = invoicedate
        ActiveSheet.Range("G4").Value = invoicenumber
        ActiveSheet.Range("F18").Value = rate
        path = "C:\Users\Dell\Desktop\feb" 
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        fileName:="C:\Users\Dell\Desktop\aa new trucking\feb\" & _
        ActiveSheet.Range("G4 ").Value & _
        Worksheets("Customers").Range("A1").Value & _
        ActiveSheet.Range("A16").Value & ".pdf", _
        OpenAfterPublish:=False

        'our label next row. Labels have a colon after their name

        nextrow:

    Next r

End Sub
Questioner
dmitriy motly
Viewed
105
分享
user11221377 2020-01-31 22:46

You can try the following code:

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

Since this is google sheets, your pdf will be saved to your google drive.

Make sure you fill in the folder id of the folder you want your files in. It is indicated in the code where you should do that.

Good luck :)

Related issues

1
Getting .json is not a function on Promise.all w/fetch
2
Javascript for loop only giving one output
3
find array value that index matches another array values
4
React
5
Communication between two components using ReactJS
6
Figma Plugin: callback on file updated
7
indexof() partial match issue
8
React useState hook and submit in multi step form
9
How do i get this firebase data to use it outside the loop?
10
How to define a font size that scales with browser zoom level?