Faced such a problem: Google forms, when saving data to a table, change in numbers the decimal separator from “comma” to “point”. As a result, instead of numbers in the table we have the text. Now I change the “point” to “comma” in the columns manually via “Menu-Edit-Find and Replace”. But I would like to automate this. According to various examples from the Internet, I wrote the following:

function onOpen() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{name : "Замена точки на запятую",functionName : "ReplaceDotToComma"}]; sheet.addMenu("Скрипты", entries); }; function ReplaceDotToComma() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Страница 2") var r = sheet.getRange("A:B"); var rws = r.getNumRows(); var cls = r.getNumColumns(); var i, j, a, fnd, rpl; fnd = "."; rpl = ","; for (i=1;i<=rws;i++) { for (j=1;j<=cls;j++) { a=r.getCell(i, j); a=a.toString().replace(fnd, rpl); r.getCell(i, j).setValue(a); } } }

But something is not working. Please help, how to write a replacement command? Ready for any help. Thanks in advance.

    4 answers 4

    Do you really need a script? Try to customize the form.

    Indeed, the format of a number is supported "normally" only for systems where the separator is a period. But you can create a text field with a value check using a regular expression with a pattern:

      \d+(?:,{1}\d+)
    • {1} required? - Nakilon
    • Well, you can always try. An example of the form is. - oshliaer

    I redid the code and get what you need. It is possible that someone else can improve it to make it more beautiful.

     function replaceOKVED(){ var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getSheetByName("transliteraciya") var sheetOKVED = ss.getSheetByName("ALFAVIT") var arrOKVED = sheetOKVED.getRange("A2:B"+sheetOKVED.getLastRow()).getDisplayValues() var arrKeys = sheet.getRange("A2:B" + sheet.getLastRow()).getDisplayValues() for(var i = 0; i < arrKeys.length; i++){ if(arrKeys[i][0].search("заменить 1") != -1){ var okvedNumber = arrKeys[i][1] for(var j = 0; j<arrOKVED.length; j++){ if(okvedNumber == arrOKVED[j][0]){ sheet.getRange(i+2, 2).setValue(arrOKVED[j][1]) break } } } else if(arrKeys[i][0].search("заменить") != -1){ var arrOkvedNumbers = arrKeys[i][1].split("") var arrOkvedNames = [] for(var l = 0; l < arrOkvedNumbers.length;l++){ for(var k = 0;k<arrOKVED.length; k++){ if(arrOkvedNumbers[l] == arrOKVED[k][0]){ arrOkvedNames.push(arrOKVED[k][1]) } } } var fullName = arrOkvedNames.join("") sheet.getRange(i+2,2).setValue(fullName) } } ss.toast("Все готово", "Внимание", 5) }

    for the menu

     function onOpen(e){ //updateXmlList() addMenu_() } function addMenu_(){ SpreadsheetApp.getUi().createMenu("МОЕ МЕНЮ") .addItem("Заменить буквы", "replaceOKVED") .addToUi() }

    1) create a table with the name "ALFAVIT" - in the first column write down what we change - in the second - what we change

    2) on any page in the column "A" we write the word "replace" in the column "B" the text of which we want to replace

    I am a beginner, and I do not have enough experience - do not judge strictly. If anyone improves the solution - I will be glad.

    • Answers are not intended to post new questions. It is better to post your problem with a separate question = / - Suvitruf ♦
    • @Suvitruf I think this is still the answer :) - Alexander Muksimov
    • @AlexanderMuksimov hmm, embarrassed "If someone improves the solution, I will be happy." If the answer, then let it be (: - Suvitruf ♦
    • I took the liberty to correct the text of your answer. Look, if I interpreted you correctly, then it makes sense to accept the edit. - Alexander Muksimov

    Google Docs Spreadsheets has SUBSTITUTE

    • one
      Please describe your decision in more detail. A detailed and high-quality response will help a lot more people who are faced with a similar problem. - Timofei Bondarev

    I suspect that a=r.getCell(i, j).GetValue(); must be