[[:tips|⇐ Tips and Tricks for DokuWiki]]

====== Google Apps Script for exporting a selected cell range to DokuWiki ======

This is a Google Apps Script to convert Google Sheets table to DokuWiki plain-text syntax.
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 sheet named 'DokuWiki'. The user will also get an email with the required text file with plain-text syntax.

Please install [[plugin:typography|typography plugin]] before using this.

If you like this code and benefit from it then please consider some [[:donate|donation]] to dokuwiki.org.

<code javascript code.js>
  function onOpen() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  options = [
  {name:"GSheets2DokuWiki", functionName:"GSheets2DokuWiki"},
  ];
  ss.addMenu("DokuWiki", options);   
  }
  
  function GSheets2DokuWiki() { 
    
    ShowDialog();
    
    // Script to convert/export the selected cells into DokuWiki format
    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 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) {
              var size = thisCell.getFontSize();
              thisCellText = "  <fs "+ size + "px >" + "✓" + "</fs>  ";
              }

                
              //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('DokuWiki'), 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 DokuWiki 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 code from the DokuWiki sheet. Thank you.');
  }
</code>