Any way to use VBA on Google spreadsheet? [closed]

问题:

Anyway to convert excel VBA to Google Appscript so that I can use it on Google spreadsheet? I need 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

回答1:

Some vba methods and properties don't have a direct equivalent in google apps script, so they have to be emulated. Note also that its better to get all your data out of the spreadsheet and manipulate it in arrays.

Here's the equivalent of what you did in VBA, translated to GAS. You didnt provide any test data, so I haven't tested it too much.

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;
}

回答2:

VBA can read/write Google Spreadsheet via GSpread.NET. It opensource library works with Google Sheets by using Microsoft Excel API.

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