Any way to use VBA in google spreadsheet?

Question

Anyway, to convert excel VBA to Google Appscript so that I can use it in a Google spreadsheet? I need the following code to convert -

Sub Test1() Application.ScreenUpdating = False Dim cell As Range, varFind As Variant With Sheets("Sheet1") For Each cell In Sheets("Sheet4").Columns(3).SpecialCells(2) Set varFind = .Columns(6).Find(What:=cell.Value, LookIn:=xlFormulas, LookAt:=xlWhole) If Not varFind Is Nothing Then .Cells(varFind.Row, 4).Value = cell.Offset(0, 2).Value Set varFind = Nothing Next cell End With Application.ScreenUpdating = True End Sub 

Answer 1

Some vba methods and properties do not have a direct equivalent in google script applications, so they need to be emulated. We also note that it is best to extract all the data from the spreadsheet and manipulate it in arrays.

Here is the equivalent of what you did in VBA, translated into GAS. You did not provide any test data, so I have not tested too much of them.

 function test() { var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); var s4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet4'); // best to read in all the values at once var v4 = s4.getDataRange().getValues(); var f4 = s4.getDataRange().getFormulas(); var v1 = s1.getDataRange().getValues(); // look at the formulas in column 3 /sheet 4 to determine if this is a constant specialCellsConstants(2,f4).forEach( function (rc) { // for each constant found, find that same constant in sheet 1, column 6 var f = findInColumn (v4[rc.row][2], v1, 5); // if it was found, then copy the value 2 columns along from sheet4 to sheet 1 colum 4 if (f) { v1[f.row][3] = v4[rc.row][4]; } }); // write back the updated values s1.getDataRange().setValues(v1); } function findInColumn(value,values,column) { // find first occurrence of value in a given column for (var i=0; i < values.length ; i++ ) { if (values[i][column] === value) { return {row:i}; } } return null; } function specialCellsConstants(column,formulas) { // emulate the specialcells function in excel and return array of the addresses of constants var results = []; for (var i = 0; i < formulas.length ; i++ ) { if (!formulas[i][column]) results.push ({row:i}); } return results; } 

Answer 2

VBA can read / write a Google spreadsheet through GSpread.NET . The open source library works with Google Sheets using the Microsoft Excel API.

Use CreateObject (GSpreadCOM.Application) instead of CreateObject (Excel.Application) .