谷歌应用程序脚本，用于将数字转换为相应的希伯来语字母，反之亦然

Question

我是谷歌应用程序脚本的新手。如果你们能帮我想出如何把英语数字写成希伯来语数字，反之亦然，只需按下一两个按钮，我将不胜感激。我试图通过replaceinSheet函数运行以下代码行，但每当数字大于10时，它就会给我一个错误：

function onOpen(){
SpreadsheetApp.getUi() 
.createMenu('convert into HEBREW numerals')
.addItem('NUM TO HEBREW', 'runReplaceInSheet')
.addItem('HEBREW TO NUM', 'runReplaceInSheet') 
.addToUi();
}

function runReplaceInSheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var values = sheet.getDataRange().getValues();  
// Replace values
replaceInSheet(values, '1', 'א‎');
replaceInSheet(values, '2', 'ב‎');
replaceInSheet(values, '3', 'ג');
replaceInSheet(values, '4', 'ד‎');
replaceInSheet(values, '5', 'ה');
replaceInSheet(values, '6', 'ו‎');
replaceInSheet(values, '7', 'ז');
replaceInSheet(values, '8', 'ח‎');
replaceInSheet(values, '9', 'ט‎');
replaceInSheet(values, '10', 'י‎');
replaceInSheet(values, '11', 'יא');
replaceInSheet(values, '12', 'יב‎');
replaceInSheet(values, '13', 'יג‎');
replaceInSheet(values, '14', 'יד');
replaceInSheet(values, '15', 'טו');
replaceInSheet(values, '16', 'טז');
replaceInSheet(values, '17', 'יז‎');
replaceInSheet(values, '18', 'יח‎‎');
replaceInSheet(values, '19', 'יט‎‎‎');
replaceInSheet(values, '20', 'כ');
replaceInSheet(values, '21', 'כא‎');
replaceInSheet(values, '22', 'כב');
replaceInSheet(values, '23', 'כג');
replaceInSheet(values, '24', 'כד');
replaceInSheet(values, '25', 'כה');
replaceInSheet(values, '26', 'כו‎');
replaceInSheet(values, '27', 'כז');
replaceInSheet(values, '28', 'כח');
replaceInSheet(values, '29', 'כט‎');
replaceInSheet(values, '30', 'ל');
replaceInSheet(values, '31', 'לא');
replaceInSheet(values, '32', 'לב‎');
replaceInSheet(values, '33', 'לג');
replaceInSheet(values, '34', 'לד');
replaceInSheet(values, '35', 'לה');
replaceInSheet(values, '36', 'לו‎');
replaceInSheet(values, '37', 'לז');
replaceInSheet(values, '38', 'לח‎');
replaceInSheet(values, '39', 'לט‎');
replaceInSheet(values, '40', 'מ');
replaceInSheet(values, '41', 'מא‎');
replaceInSheet(values, '42', 'מב‎');
replaceInSheet(values, '43', 'מג');
replaceInSheet(values, '44', 'מד');
replaceInSheet(values, '45', 'מה');
replaceInSheet(values, '46', 'מו');
replaceInSheet(values, '47', 'מז');
replaceInSheet(values, '48', 'מח‎');
replaceInSheet(values, '49', 'מט');
replaceInSheet(values, '50', 'נ');
replaceInSheet(values, '51', 'נא‎');
replaceInSheet(values, '52', 'נב');
replaceInSheet(values, '53', 'נג');
replaceInSheet(values, '54', 'נד');
replaceInSheet(values, '55', 'נה');
replaceInSheet(values, '56', 'נו‎');
replaceInSheet(values, '57', 'נז');
replaceInSheet(values, '58', 'נח');
replaceInSheet(values, '59', 'נט');
replaceInSheet(values, '60', 'ס');
replaceInSheet(values, '61', 'סא‎');
replaceInSheet(values, '62', 'סב');
replaceInSheet(values, '63', 'סג');
replaceInSheet(values, '64', 'סד');
replaceInSheet(values, '65', 'סה');
replaceInSheet(values, '66', 'סו');
replaceInSheet(values, '67', 'סז');
replaceInSheet(values, '68', 'סח‎');
replaceInSheet(values, '69', 'סט');
replaceInSheet(values, '70', 'ע');
replaceInSheet(values, '71', 'עא');
replaceInSheet(values, '72', 'עב');
replaceInSheet(values, '73', 'עג');
replaceInSheet(values, '74', 'עד‎');
replaceInSheet(values, '75', 'עה');
replaceInSheet(values, '76', 'עו');
replaceInSheet(values, '77', 'עז');
replaceInSheet(values, '78', 'עח‎');
replaceInSheet(values, '79', 'עט‎');
replaceInSheet(values, '80', 'פ');
replaceInSheet(values, '81', 'פא');
replaceInSheet(values, '82', 'פב');
replaceInSheet(values, '83', 'פג');
replaceInSheet(values, '84', 'פד‎');
replaceInSheet(values, '85', 'פה');
replaceInSheet(values, '86', 'פו');
replaceInSheet(values, '87', 'פז');
replaceInSheet(values, '88', 'פח');
replaceInSheet(values, '89', 'פט');
replaceInSheet(values, '90', 'צ');
replaceInSheet(values, '91', 'צא');
replaceInSheet(values, '92', 'צב');
replaceInSheet(values, '93', 'צג');
replaceInSheet(values, '94', 'צד');
replaceInSheet(values, '95', 'צה');
replaceInSheet(values, '96', 'צו');
replaceInSheet(values, '97', 'צז');
replaceInSheet(values, '98', 'צח‎');
replaceInSheet(values, '99', 'צט');
// Write all updated values to the sheet, at once
sheet.getDataRange().setValues(values);
}
function replaceInSheet(values, to_replace, replace_with) {
//loop over the rows in the array
for(var row in values){
//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value) {
return original_value.toString().replace(to_replace,replace_with);
});
//replace the original row values with the replaced values
values[row] = replaced_values;
}
}

此外，如何更改代码的第一部分，使其仅针对特定的数据范围(例如，B2:E5(，而不是整个表单？

Answer 1

我了解到您想要修改该代码以只影响特定的数据范围(即B2:E5(。在这种情况下，你只需要在得到范围时解决它。这是原始代码：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var values = sheet.getDataRange().getValues();

要选择特定的范围而不是数据范围，让我们用Sheet.getDataRange()代替Sheet.getRange(2, 2, 3, 3)。这将返回B2:E5。最终结果应该是：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var values = sheet.getRange(2, 2, 3, 3).getValues();