任何方式在Google电子表格上使用VBA？ [英] Any way to use VBA on Google spreadsheet?

问题描述

无论如何将excel VBA转换为Google Appscript，以便我可以在Google电子表格中使用它？我需要以下代码来转换 -

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

推荐答案

某些vba方法和属性在谷歌应用脚​​本中没有直接的等价物，所以他们必须被仿效。还要注意，它更好地将所有数据从电子表格中获取，并将其数组操作。

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.

这相当于您在VBA中的翻译成GAS的功能。你没有提供任何测试数据，所以我没有测试太多。

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

这篇关于任何方式在Google电子表格上使用VBA？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！