I trying to build list of my local employee.
Currently using Excel VBA and it is working fine but my superior wants this to be online access. So now I am using Google Sheets and Google App Script. I am facing problem to do the search function. My code in VBA as follow-
Dim wb As Workbook
Dim whs As Worksheet
Dim NoKadPengenalan As String
Dim lastrowIC As Long
Set wb = ThisWorkbook
Set whs = wb.Sheets("Data")
NoKadPengenalan = Trim(txtCarianIC.Text)
lastrowIC = whs.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrowIC
If whs.Cells(i, 2).Value = txtCarianIC.Text Then
txtNama.Text = whs.Cells(i, 1).Value
txtIC.Text = whs.Cells(i, 2).Value
txtAlamat.Text = whs.Cells(i, 4).Value
txtTel.Text = whs.Cells(i, 5).Value
cmbPL.Value = whs.Cells(i, 6).Value
cmbZon.Value = whs.Cells(i, 7).Value
cmbStatus.Value = whs.Cells(i, 8).Value
Next
End Sub
How do I change those code in Google App script and return values to userform in the Google sheets.
I have try below codes but it returns nothing
var searchID= 0;
function Search()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("User Form"); //User Entry Form
var str = formS.getRange("C3").getValue();
var values = ss.getSheetByName(dataS).getDataRange().getValues();
for (var i=0; i<values.length; i++)
{
var rows = values[i];
if (rows[searchID] == str)
{
formS.getRange("C6").setValue(rows[0]);
formS.getRange("C8").setValue(rows[1]);
formS.getRange("C10").setValue(rows[2]);
formS.getRange("C12").setValue(rows[3]);
formS.getRange("C17").setValue(rows[4]);
formS.getRange("F6").setValue(rows[5]);
formS.getRange("F8").setValue(rows[6]);
formS.getRange("F10").setValue(rows[7]);
formS.getRange("F12").setValue(rows[8]);
formS.getRange("F14").setValue(rows[9]);
formS.getRange("F16").setValue(rows[10]);
formS.getRange("F19").setValue(rows[11]);
formS.getRange("G19").setValue(rows[12]);
formS.getRange("H19").setValue(rows[13]);
}
}
}
Just a guess. The rows[searchID] is rows[0]. As far as I can tell first column contains names. So it's a someone's name.
str is some ID (from cell "C3").
So here if (rows[searchID] == str) you're trying to compare the name from first column with ID from cell "C3". It doesn't make sense. You will get false always unless someone has a name like '96081001649'.