I am kinda ok with the VBA but I have very less clue about the Apps Script as it uses a different coding language.
Is there a way to transform this code for the Apps Script? Here are the details of my code in VBA and what I tried in the Apps Script:
Function matchstring(input1) as String
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row + 1
row2 = ws.Range("E1").Row
col2 = ws.Range("E1").Column
c = False
row3 = row2
row1 = input1.Row
col1 = input1.Column
While c = False And row3 < lastrow
'Check A'
a = input1.Value
b = ws.Cells(row3, col2).Value
d = ws.Cells(row3, col2 - 3).Value
e = Cells(row1, col1 - 3).Value
i = 1
'Check B'
f = ws.Cells(row3, col2).Value
g = ws.Cells(row3, col2 - 1).Value
h = "Spain"
'Check C'
i = ws.Cells(row3, col2).Value
j = ws.Cells(row3, col2 - 2).Value
k = Cells(row1, col1 - 2).Value
If InStr(1, a, b, 1) And e = d And j = k And g = h Then
c = True
matchstring = b
Else
row3 = row3 + 1
c = False
End If
Wend
End Function
However, when I tried to implement this code below in the App Script, the code always returns errors:
function matchstring(input1) {
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
var lastrow = ws.getRange("B" + ws.getLastRow()).getNextDataCell(SpreadsheetApp.Direction.UP).getRow() + 1;
var row2 = ws.getRange("E1").getRow();
var col2 = ws.getRange("E1").getColumn();
var c = false;
var row3 = row2;
var row1 = input1.getRow;
var col1 = input1.getColumn;
while (!c && row3 < lastrow) {
// Check A
var a = input1.getA1Notation;
var b = ws.getRange(row3, col2).getA1Notation();
var d = ws.getRange(row3, col2 - 3).getA1Notation();
var e = ws.getRange(row1, col1 - 3).getA1Notation();
// Check B
var g = ws.getRange(row3, col2 - 1).getA1Notation();
var h = "Spain";
// Check C
var j = ws.getRange(row3, col2 - 2).getA1Notation();
var k = ws.getRange(row1, col1 - 2).getA1Notation();
if (a.indexOf(b) !== -1 && e === d && j === k && g === h) {
c = true;
return b;
} else {
row3++;
c = false;
}
}
}
The first error I received is related to the input1 (input1.getRow is not a function) because I think it is not yet defined in the Apps Script, but works in the GSheet.
Then I received a second error:
Exception: The parameters (null,number) don’t match the method signature for SpreadsheetApp.Sheet.getRange. (line 15).
This code could be totally wrong because I have very less knowledge on how Apps Script works.
Thank you in advance and I am always open to give further details.
