google_sheets_macro
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
google_sheets_macro [2022-01-08 13:41] – 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 ' | ||
- | 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 ' | ||
- | <code javascript> | ||
- | function onOpen() { | ||
- | |||
- | var ss = SpreadsheetApp.getActiveSpreadsheet(); | ||
- | options = [ | ||
- | {name:" | ||
- | ]; | ||
- | ss.addMenu(" | ||
- | }; | ||
- | |||
- | |||
- | function GSheets2KMS() { | ||
- | ShowDialog(); | ||
- | // Script to convert/ | ||
- | var ss = SpreadsheetApp.getActiveSpreadsheet(); | ||
- | ss.setActiveSheet(ss.getSheetByName(' | ||
- | var activeSheet = SpreadsheetApp.getActiveSheet(); | ||
- | var selected = activeSheet.getSelection(); | ||
- | var selectedrange = selected.getActiveRange(); | ||
- | // | ||
- | // | ||
- | // | ||
- | var lastrow = selectedrange.getLastRow(); | ||
- | var lastcol = selectedrange.getLastColumn(); | ||
- | // | ||
- | // | ||
- | //var selectedrange = activeSheet.getRange(' | ||
- | var rows=selectedrange.getNumRows(); | ||
- | var cols=selectedrange.getNumColumns(); | ||
- | var firstrow = lastrow - rows + 1; | ||
- | var firstcol = lastcol - cols + 1; | ||
- | // | ||
- | // | ||
- | var wikiText=""; | ||
- | var attachments = []; | ||
- | |||
- | | ||
- | { | ||
- | for(thisCol = firstcol; thisCol< | ||
- | { | ||
- | var thisCell = activeSheet.getRange(thisRow, | ||
- | var thisCellText = SpreadsheetApp.getActiveSheet().getRange(thisRow, | ||
- | //if it's an empty cell then make it a space (to avoid merging cells) | ||
- | if (thisCell.isPartOfMerge() == false && 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' | ||
- | if (thisCell.getRichTextValue() != null) | ||
- | { | ||
- | var urlLink = thisCell.getRichTextValue().getLinkUrl(); | ||
- | if (urlLink != null){ thisCellText = " | ||
- | } | ||
- | | ||
- | // | ||
- | if(thisCell.getFontWeight() == ' | ||
- | thisCellText = " | ||
- | } | ||
- | if(thisCell.getFontStyle() == ' | ||
- | | ||
- | | ||
- | if(thisCell.getFontLine() == ' | ||
- | thisCellText = " | ||
- | } | ||
- | if(thisCell.getFontLine() == ' | ||
- | thisCellText = "< | ||
- | } | ||
- | | ||
- | // Color the text if not black | ||
- | if(thisCell.getFontColor() != '# | ||
- | var color = thisCell.getFontColor(); | ||
- | thisCellText = "< | ||
- | } | ||
- | |||
- | // Color the background if not white | ||
- | // | ||
- | if(thisCell.getBackground() != '# | ||
- | var bgcolor = thisCell.getBackground(); | ||
- | thisCellText = "< | ||
- | } | ||
- | |||
- | //Change Font if not the default font (Arial) | ||
- | // | ||
- | if(thisCell.getFontFamily() != ' | ||
- | var font = thisCell.getFontFamily(); | ||
- | thisCellText = "< | ||
- | } | ||
- | |||
- | // Change Font size if not the default font-size (12) | ||
- | if(thisCell.getFontSize() != 12) { | ||
- | var size = thisCell.getFontSize(); | ||
- | thisCellText = "< | ||
- | } | ||
- | // Check Box | ||
- | if(thisCell.isChecked() == true) { | ||
- | thisCellText = " | ||
- | } | ||
- | |||
- | //alignment | ||
- | if(thisCell.getHorizontalAlignment() == ' | ||
- | thisCellText = thisCellText + " | ||
- | } | ||
- | if(thisCell.getHorizontalAlignment() == ' | ||
- | thisCellText = " | ||
- | } | ||
- | if(thisCell.getHorizontalAlignment() == ' | ||
- | thisCellText = " | ||
- | } | ||
- | } | ||
- | | ||
- | //replace embedded newlines with backslashes | ||
- | | ||
- | | ||
- | //add this cell to wiki output string | ||
- | if (thisRow == firstrow ) | ||
- | { | ||
- | // | ||
- | | ||
- | } | ||
- | else { | ||
- | wikiText = wikiText + " | ||
- | } | ||
- | } | ||
- | //end this row | ||
- | if (thisRow == firstrow ) { | ||
- | //heading row or colored cell | ||
- | wikiText = wikiText + " | ||
- | else{ | ||
- | wikiText = wikiText + " | ||
- | } | ||
- | } | ||
- | var ss = SpreadsheetApp.getActiveSpreadsheet(); | ||
- | ss.setActiveSheet(ss.getSheetByName(' | ||
- | ss.getRange(' | ||
- | | ||
- | | ||
- | |||
- | |||
- | / | ||
- | | ||
- | MailApp.sendEmail(Session.getActiveUser().getEmail(), | ||
- | " | ||
- | "Your converted Dokuwiki document is attached (converted from " | ||
- | " | ||
- | { " | ||
- | CloseDialog(); | ||
- | } | ||
- | |||
- | function ShowDialog() { | ||
- | var htmlOutput = HtmlService | ||
- | .createHtmlOutput() | ||
- | .setWidth(250) | ||
- | .setHeight(25); | ||
- | SpreadsheetApp.getUi().showModalDialog(htmlOutput,' | ||
- | }; | ||
- | |||
- | function CloseDialog() { | ||
- | var ui = SpreadsheetApp.getUi(); | ||
- | var html = HtmlService.createHtmlOutput("< | ||
- | SpreadsheetApp.getUi().showModalDialog(html,' | ||
- | }; | ||
- | | ||
- | </ |