Created
August 5, 2021 18:13
-
-
Save rapartipoorna/e6cb07b968e705124aa4f1abec205aa6 to your computer and use it in GitHub Desktop.
[ Apps script ] It will convert first column rows into columns in spreadsheet .it should be bounded to spreadsheet. when you run script menu will be created .and menu will prompts to ask 'sheet name' and 'number of rows' to give appropriate result.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> | |
| <script> | |
| function closeWindow() { | |
| google.script.host.close(); | |
| } | |
| function showError(error) { | |
| document.getElementById("error").innerHTML = error; | |
| } | |
| function showConfig(params) { | |
| if (params.label !== "") | |
| document.getElementById("label").value = params.label; | |
| return; | |
| } | |
| function validate() { | |
| var label = document.getElementById("label").value; | |
| var label1 = document.getElementById("label1").value; | |
| if (label.trim() === "" || label1.trim() === "" ) { | |
| showError("Please enter Valid details.."); | |
| return; | |
| } | |
| // if (label1.trim() === "") { | |
| // showError("Please enter Number of rows.."); | |
| // return; | |
| // } | |
| else { | |
| showError("Saving configuration, please wait.."); | |
| var params = { | |
| label: label , label1:label1 | |
| }; | |
| google.script.run.withSuccessHandler(showError).saveConfig(params); | |
| } | |
| } | |
| </script> | |
| <form> | |
| <div class="form-group block"> | |
| <p style="margin-bottom:4px;">Enter Sheet Name:</p> | |
| <input type="text" id="label" name="label" placeholder="Enter Sheet Name.." style="width: 250px;" /> | |
| <p style="margin-bottom:4px;">Enter Number of Rows to convert:</p> | |
| <input type="number" id="label1" name="label" placeholder="Enter Number of rows.." style="width: 250px;" /> | |
| </div> | |
| <p> | |
| <input class="blue" type="button" value="Save" onclick="validate()" /> | |
| <input class="green" type="button" value="Close window" onclick="google.script.host.close()" /> | |
| </p> | |
| <p class="error" id="error"></p> | |
| </form> | |
| <script> | |
| google.script.run.withSuccessHandler(showConfig).getConfig(); | |
| </script> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function onOpen() { | |
| var menu = [ | |
| {name: "Configure", functionName: "config_"}, | |
| null | |
| ]; | |
| SpreadsheetApp.getActiveSpreadsheet().addMenu("➪ SingleColumn Rows to Columns", menu); | |
| } | |
| function config_() { | |
| var html = HtmlService.createHtmlOutputFromFile('config') | |
| .setTitle("Conversion of Rows into columns") | |
| .setWidth(300).setHeight(200).setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
| var ss = SpreadsheetApp.getActive(); | |
| ss.show(html); | |
| } | |
| function newRange(params) { | |
| var sheetName = params.label | |
| var rows = params.label1 | |
| console.log(params.label1) | |
| var ss = SpreadsheetApp.getActiveSpreadsheet() | |
| // var sheet = ss.getSheets()[0]; | |
| var sheet = ss.getSheetByName(sheetName) | |
| var cell = sheet.getRange(1,1) | |
| var cellData = sheet.getDataRange().getValues() | |
| sheet.deleteColumn(1) | |
| var j = m = k = 0 | |
| var count = 1 | |
| for(var i=1;i<cellData.length+1;i++){ | |
| if(count%rows===0){ | |
| j = j+1 | |
| m = 0 | |
| k = m | |
| count = 1 | |
| } | |
| else{ | |
| m = m+1 | |
| } | |
| cell.offset(j, k).setValue(cellData[i-1]); | |
| k=k+1 | |
| count = count+1 | |
| } | |
| } | |
| function saveConfig(params) { | |
| try { | |
| newRange(params) | |
| return "Now script is Active . Please close this window."; | |
| } catch (e) { | |
| return "ERROR: " + e.toString(); | |
| } | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment