我在谷歌应用程序脚本中编写了一个脚本来搜索工作表中的值并根据用户输入的值更新行。它适用于谷歌表。但是当我将文档下载为 excel 文件时，我希望脚本是偶数下载并转换为 VBA 宏，但这并没有发生，因为我没有任何编写 VBA 脚本的经验，是否有任何可用于将 .gs 转换为 VBA 的工具。如果有帮助，以下是功能：
function test() {
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
var spreadsheet = SpreadsheetApp.getActive();
sheet2.getRange('B5:B7').activate();
var idForSearch = sheet2.getRange("B5").getValue();
var data = sheet1.getDataRange().getValues();
var rownumber;
for(var i = 0; i<data.length;i++){
if(data[i][0] == idForSearch){ //[0] because column A
Logger.log((i+1))
rownumber= i+1;
}
}
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
spreadsheet.getRange('A'+rownumber+':'+'C'+rownumber).activate();
spreadsheet.getRange('Sheet2!B5:B7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};
提前感谢您的帮助。
请测试下一个代码。我没有你的数据要处理，我只是理论上做的，没有测试......
Private Sub testConvert()
Dim actWork As Workbook, sheet1 As Worksheet, sheet2 As Worksheet
Dim data As Variant, i As Long, rownumber As Long, rng As Range
Set actWork = ActiveWorkbook
Set sheet1 = actWork.Sheets("Sheet1")
Set sheet2 = actWork.Sheets("Sheet2")
actWork.Activate
idForSearch = shee2.Range("B5").Value
data = sheet1.Range("B5:B7").Value 'not necessary to be activated
For i = 1 To UBound(data) 'in VBA such an array (created from a range) is 1 based...
If UCase(data(i,1)) = UCase(idForSearch) Then 'VBA is case sensitive
WriteLog i 'you must have a function ('WriteLog') able to do it...
rownumber = i
'if you have only one occurrence, it is good to exit the loop to save time (not for this specific case of 3 times):
Exit For
'If many occurrences may be, you just comment the line above...
End If
Next i
sheet1.Activate 'not necessary
Set rng = sheet1.Range("A" & rownumber & ":" & "C" & rownumber).Select
sheet2.Range("B5:B7").Value = WorksheetFunction.Transpose(rng.Value)
End Sub
最后一个编码行转置...的sheet1列范围的水平范围sheet2
WriteLog如果你告诉我更多细节，我可以帮助你创建函数：它必须是什么类型的日志（文本文件），在要创建的路径上，找到等。你想添加，i附加值，还是如果已经记录了任何值，您想重写现有的吗？
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句