Converting from VBA to Java script

Hi, can anyone help me convert this macro to java? I need to print a form using a command button and the only way is through google sheet. These are the macros:

Sub Print()
Application.ScreenUpdating = False
LR = Sheets(1).Range("A3000").End(xlUp).Row
For i = Sheets(2).Cells(7, 18).Value To Sheets(2).Cells(7, 19).Value

    If Sheets(1).Cells(i, 101) = "GOOD" Or Sheets(1).Cells(i, 101) = "Excellent" Then
        Sheets(2).Cells(3, 3) = Sheets(1).Cells(i, 3)
        Sheets(2).Cells(3, 13) = Sheets(1).Cells(i, 2)
        Sheets(2).Cells(9, 3) = Sheets(1).Cells(i, 13)
        Sheets(2).Cells(9, 4) = Sheets(1).Cells(i, 22)
        Sheets(2).Cells(9, 5) = Sheets(1).Cells(i, 31)
        Sheets(2).Cells(9, 6) = Sheets(1).Cells(i, 40)
        Sheets(2).Cells(9, 7) = Sheets(1).Cells(i, 51)
        Sheets(2).Cells(9, 8) = Sheets(1).Cells(i, 57)
        Sheets(2).Cells(9, 9) = Sheets(1).Cells(i, 62)
        Sheets(2).Cells(9, 10) = Sheets(1).Cells(i, 67)
        Sheets(2).Cells(9, 11) = Sheets(1).Cells(i, 72)
        Sheets(2).Cells(11, 10) = Sheets(1).Cells(i, 102)
        Sheets(2).Cells(11, 8) = Sheets(1).Cells(i, 101)
        Sheets(2).Cells(9, 12).Formula = "=Sum(c9:k9)"
        Sheets(2).Cells(9, 13) = Sheets(1).Cells(i, 82)

End If

Next i
Application.ScreenUpdating = True

End Sub

Java or JavaScript?


I think I can get you to the print portion, but that’s going to take some more research since it doesn’t look as straight forward.

It should be something close to this…

  • The sheet1/sheet2 creates an object instead of having to access the the SpreadsheetApp.GetActiveSheets().GetSheetName(“sheet1”) each time.
  • getRange() is the equivalent of Cells, and can use the row, column notation of the cell name (A1) notation.
  • getValue and setValue hopefully are self-explanatory
Function Print() {
	// Get sheets
	var sheet1 = SpreadsheetApp.GetActiveSheets().GetSheetName("name1");
	var sheet2 = SpreadsheetApp.GetActiveSheets().GetSheetName("name2");

	If (sheet1.getRange(i, 101).getValue() == "GOOD" || sheet1.getRange(i, 101).getValue() == "Excellent") {
		sheet2.getRange(3, 3).setValue(sheet1.getRange(i, 3).getValue());
		sheet2.getRange(3, 13).setValue(sheet1.getRange(i, 2).getValue());
		sheet2.getRange(9, 3).setValue(sheet1.getRange(i, 13).getValue());
		sheet2.getRange(9, 4).setValue(sheet1.getRange(i, 22).getValue());
		sheet2.getRange(9, 5).setValue(sheet1.getRange(i, 31).getValue());
		sheet2.getRange(9, 6).setValue(sheet1.getRange(i, 40).getValue());
		sheet2.getRange(9, 7).setValue(sheet1.getRange(i, 51).getValue());
		sheet2.getRange(9, 8).setValue(sheet1.getRange(i, 57).getValue());
		sheet2.getRange(9, 9).setValue(sheet1.getRange(i, 62).getValue());
		sheet2.getRange(9, 10).setValue(sheet1.getRange(i, 67).getValue());
		sheet2.getRange(9, 11).setValue(sheet1.getRange(i, 72).getValue());
		sheet2.getRange(11, 10).setValue(sheet1.getRange(i, 102).getValue());
		sheet2.getRange(11, 8).setValue(sheet1.getRange(i, 101).getValue());
		sheet2.getRange(9, 12).setFormula("=SUM(c9:k9)");
		sheet2.getRange(9, 13).setValue(sheet1.getRange(i, 82).getValue());
		// Incomplete - this gets the range, but printing is not clean from google sheets
		//				will need to search for code which handles this portion.

This is Google Sheet

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.