Differences

This shows you the differences between two versions of the page.

--- google_sheets_macro [2022-01-08 13:58] – sarabjeet
+++ google_sheets_macro [2023-04-17 21:56] (current) – moved Aleksandr
@@ Line 1: / Line 1: @@
-===== Google Apps Script for exporting a selected cell range to Dokuwiki=====
-This is a google apps script to convert google sheets table to dokuwiki text editor format.
-Create a google spreadsheet with two worksheets one named 'Table' and other named 'Dokuwiki'.
-Select the data in the sheet named table and run the script and you will get the required text code for the selected range in the 'Dokuwiki' Sheet. The user will also get an email with the required text file.
-Please install [[plugin:typography|typography plugin]] before using this.
-<code javascript>
-  function onOpen() {
-  var ss = SpreadsheetApp.getActiveSpreadsheet();
-  options = [
-  {name:"GSheet2KMS", functionName:"GSheets2KMS"},
-  ];
-  ss.addMenu("Shubhkarma", options);
-};
-function GSheets2KMS() {
-    ShowDialog();
-    // Script to convert/export the selected cells into a KMS table
-    var ss = SpreadsheetApp.getActiveSpreadsheet();
-    ss.setActiveSheet(ss.getSheetByName('Table'), true);
-    var activeSheet = SpreadsheetApp.getActiveSheet();
-    var selected = activeSheet.getSelection();
-    var selectedrange = selected.getActiveRange();
-    //Logger.log('Active Range: ' + selectedrange.getA1Notation());
-    //Logger.log('Rows: ' + selectedrange.getNumRows());
-    //Logger.log('Columns: ' + selectedrange.getNumColumns());
-    var lastrow = selectedrange.getLastRow();
-    var lastcol = selectedrange.getLastColumn();
-    //Logger.log('LastRow: ' + selectedrange.getLastRow());
-    //Logger.log('LastColumn: ' + selectedrange.getLastColumn());
-    //var selectedrange = activeSheet.getRange('A1:Q10');
-    var rows=selectedrange.getNumRows();
-    var cols=selectedrange.getNumColumns();
-    var firstrow = lastrow - rows + 1;
-    var firstcol = lastcol - cols + 1;
-    //Logger.log('FirstRow: ' + firstrow);
-    //Logger.log('FirstColumn: ' + firstcol);
-    var wikiText="";
-    var attachments = [];
- for(thisRow = firstrow; thisRow<=lastrow; thisRow++)
-  {
-    for(thisCol = firstcol; thisCol<=lastcol; thisCol++)
-        {
-            var thisCell = activeSheet.getRange(thisRow, thisCol);
-            var thisCellText = SpreadsheetApp.getActiveSheet().getRange(thisRow, thisCol).getValue();
-            //if it's an empty cell then make it a space (to avoid merging cells)
-            if (thisCell.isPartOfMerge() == false && thisCellText == "") { thisCellText = " "}
-            else if (thisCellText != "") {
-            //replace Dokuwiki syntax signs to NoWiki
-               // thisCellText = thisCellText.toString().replaceAll("*", "%%*%%");
-                thisCellText = thisCellText.toString().replaceAll("|", "%%|%%");
-                thisCellText = thisCellText.toString().replaceAll("^", "%%^%%");
-            //Convert the hyperlink of the cell & add the cell content as the hyperlink's name
-                  if (thisCell.getRichTextValue() != null)
-                  {
-                  var urlLink = thisCell.getRichTextValue().getLinkUrl();
-                  if (urlLink != null){ thisCellText = "  [[" + urlLink + "|" + thisCellText + "]]  " }
-                  }
-            //formatting
-              if(thisCell.getFontWeight() == 'bold'){
-                  thisCellText = "**" + thisCellText + "**";
-                 }
-              if(thisCell.getFontStyle() == 'italic') {
-                 thisCellText = "//" + thisCellText + "//";
-                 }
-              if(thisCell.getFontLine() == 'underline') {
-              thisCellText = "__" + thisCellText + "__";
-                }
-              if(thisCell.getFontLine() == 'line-through') {
-              thisCellText = "<del>" + thisCellText + "</del>";
-                }
-              // Color the text if not black
-              if(thisCell.getFontColor() != '#000000') {
-              var color = thisCell.getFontColor();
-              thisCellText = "<fc "+ color + ">" + thisCellText + "</fc>";
-              }
-              // Color the background if not white
-              //Logger.log(thisCell.getBackground());
-              if(thisCell.getBackground() != '#ffffff') {
-              var bgcolor = thisCell.getBackground();
-              thisCellText = "<bg "+ bgcolor + ">" + thisCellText + "</bg>";
-              }
-              //Change Font if not the default font (Arial)
-              //Logger.log(thisCell.getFontFamily());
-              if(thisCell.getFontFamily() != 'Arial') {
-              var font = thisCell.getFontFamily();
-              thisCellText = "<ff "+ font + ">" + thisCellText + "</ff>";
-              }
-              // Change Font size if not the default font-size (12)
-              if(thisCell.getFontSize() != 12) {
-              var size = thisCell.getFontSize();
-              thisCellText = "<fs "+ size + "px >" + thisCellText + "</fs>";
-              }
-              // Check Box
-              if(thisCell.isChecked() == true) {
-              thisCellText = "  " + "✓" + "  ";
-              }
-              //alignment
-              if(thisCell.getHorizontalAlignment() == 'left') {
-                thisCellText = thisCellText + "  ";
-              }
-              if(thisCell.getHorizontalAlignment() == 'center') {
-                thisCellText = "  " + thisCellText + "  ";
-              }
-              if(thisCell.getHorizontalAlignment() == 'right') {
-                thisCellText = "  " + thisCellText;
-              }
-            }
-            //replace embedded newlines with backslashes
-               thisCellText = thisCellText.toString().replaceAll(String.fromCharCode(10), "\\\\ ");
-            //add this cell to wiki output string
-               if (thisRow == firstrow )
-               {
-               //heading row or colored cell
-               wikiText = wikiText + "^" + thisCellText;
-               }
-               else {
-                    wikiText = wikiText + "|" + thisCellText;
-                    }
-        }
-            //end this row
-              if (thisRow == firstrow ) {
-                //heading row or colored cell
-              wikiText = wikiText + "^" + String.fromCharCode(13);}
-              else{
-                  wikiText = wikiText + "|" + String.fromCharCode(13);
-                  }
-  }
-  var ss = SpreadsheetApp.getActiveSpreadsheet();
-  ss.setActiveSheet(ss.getSheetByName('KMS'), true);
-  ss.getRange('A1').setValue(wikiText);
-/*attachments.push({"fileName":SpreadsheetApp.getActiveSpreadsheet().getName()+".txt", "mimeType": "text/plain", "content": wikiText});
-  MailApp.sendEmail(Session.getActiveUser().getEmail(),
-                    "[gSheets2Dokuwiki] "+SpreadsheetApp.getActiveSpreadsheet().getName(),
-                    "Your converted Dokuwiki document is attached (converted from "+SpreadsheetApp.getActiveSpreadsheet().getUrl()+")"+
-                    "\n\n\n",
-                    { "attachments": attachments });*/
-  CloseDialog();
-  }
-function ShowDialog() {
-  var htmlOutput = HtmlService
-    .createHtmlOutput()
-    .setWidth(250)
-    .setHeight(25);
-  SpreadsheetApp.getUi().showModalDialog(htmlOutput,'Please wait till we convert the selected cells to KMS text format.');
-};
-function CloseDialog() {
-  var ui = SpreadsheetApp.getUi();
-  var html = HtmlService.createHtmlOutput("<script>google.script.host.close();</script>");
-  SpreadsheetApp.getUi().showModalDialog(html,'Please copy text from the KMS sheet and paste it in the KMS text editor. Thank you. Shubhkarma Software Solutions.');
-};
-</code>