Converting from VBA to Java script

JavaScript
1

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)
        Sheets(2).Range("A1:p15").PrintOut

End If

Next i
Application.ScreenUpdating = True




End Sub
2

Java or JavaScript?

2 Likes
3

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.
		sheet2.getRange("A1:P15).PrintOut
	}
}
4

https://docs.google.com/spreadsheets/d/1AR6vF4Seps7RWSo5EojcnTbQnBOo_rQ-y61slJi3_sk/edit?usp=sharing

5

This is Google Sheet

6

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