OO-PG0020

Plug-In: Import Copied Spreadsheet

A specialized command for importing tab-return-delimited spreadsheet data from the clipboard. TIP: For best results, start with a new blank empty outline.

Columns and rows are added as required, with the option for the outline column (topic) to be used for row headers.

A form is presented prior to input enabling the user to indicate column formatting (numeric) and to indicate whether the copied data contains row and/or column headers.

Return to: OmniOutliner Plug-In Collection

Column Title Alignment
Copy Download Plug-In Install Plug-In

/*{
	"type": "action",
	"targets": ["omnioutliner"],
	"author": "Otto Automator",
	"identifier": "com.omni-automation.oo.import-copied-spreadsheet",
	"version": "1.1",
	"description": "A specialized command for importing tab-return-delimited spreadsheet data from the clipboard.",
	"label": "Import Copied Spreadsheet",
	"shortLabel": "Import Spreadsheet",
	"paletteLabel": "Import Spreadsheet",
	"image": "square.and.arrow.down.fill"
}*/
(() => {
	const action = new PlugIn.Action(async function(selection, sender){
		try {
			function createUtterance(textToSpeak){
				langCode = Speech.Voice.currentLanguageCode
				voiceObj = Speech.Voice.withLanguage(langCode)
				utterance = new Speech.Utterance(textToSpeak)
				utterance.voice = voiceObj
				utterance.rate = Speech.Utterance.defaultSpeechRate
				return utterance
			}
			var synthesizer = new Speech.Synthesizer()

			try {document.name} catch(err){
				throw {name:"Missing Resource",message:"No outline document is open."}
			}

			// CHECK CLIPBOARD CONTENT
			var clipText = Pasteboard.general.string
			if(!clipText.includes("\t")){
				throw {
					name: "Missing Data",
					message: "There is no tab-delimited spreadsheet data on the clipboard."
				}
			}

			// CREATE FORM 
			inputForm = new Form()

			formats = ["Text (abc)", "Number (123)", "Decimal (123.00)", "Percent (123%)", "Decimal Percent (123.12%)", "Decimal Separators (1,234.12)", "Currency"]
			formatMenu = new Form.Field.Option(
				"formatType", 
				"Format", 
				[0, 1, 2, 3, 4, 5, 6], 
				formats, 
				1
			)

			columnHeadersCheckbox = new Form.Field.Checkbox(
				"firstRowIsColHeaders",
				"1st data row is column headers",
				true
			)

			rowHeadersCheckbox = new Form.Field.Checkbox(
				"firstColumnIsRowHeaders",
				"1st data column is row headers",
				true
			)

			inputForm.addField(formatMenu)
			inputForm.addField(columnHeadersCheckbox)
			inputForm.addField(rowHeadersCheckbox)

			// PRESENT FORM WITH SPOKEN PROMPT
			formPrompt = "Format for added columns:"
			formButtom = "Continue"
			spokenPrompt = "Please indicate the format and parameters for the added columns."

			utterance = createUtterance(spokenPrompt)
			synthesizer.speakUtterance(utterance)

			formObject = await inputForm.show(formPrompt, formButtom)
			synthesizer.stopSpeaking(Speech.Boundary.Word)

			// RETRIEVE FORM RESULTS  
			var firstRowIsColHeaders = formObject.values["firstRowIsColHeaders"]
			var firstColumnIsRowHeaders = formObject.values["firstColumnIsRowHeaders"]
			var formatType = formObject.values['formatType']
			switch(formatType) {
				case 0:
					var columnType = Column.Type.Text
					var fmtr = null
					break;
				case 1:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.plain
					break;
				case 2:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.decimal
					break;
				case 3:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.percent
					break;
				case 4:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.percentWithDecimal
					break;
				case 5:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.thousandsAndDecimal
					break;
				case 6:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.currency()
					break;
				default:
					var columnType = Column.Type.Number
					var fmtr = Formatter.Decimal.plain
					break;
			}

			// CLEAN CLIPBOARD TEXT TO ARRAY OF ROW DATA
			dataRows = clipText.split("\n")
			var tableData = []
			dataRows.forEach(dataRow => {
				tableData.push(dataRow.split("\t"))
			})

			// ADD COLUMNS AS REQUIRED
			var tree = document.outline
			var editor = document.editors[0]
			editor.autosizeTopicColumn = true

			if(firstRowIsColHeaders){
				columnTitles = tableData[0]
				tableData.shift()
			}

			var numRows = tableData.length
			var numCols = tableData[0].length

			var columnRefs = []
			for (i = 0; i < numCols; i++) { 
				if(firstColumnIsRowHeaders && firstRowIsColHeaders && i === 0){
					columnRefs.push(outlineColumn)
					outlineColumn.title = columnTitles[0]
				} else {
					newCol = tree.addColumn(
						columnType,
						editor.afterColumn(null),
						column => {
							if(firstRowIsColHeaders){
								column.title = columnTitles[i]
							}
							if(fmtr){column.formatter = fmtr}
						}
					)
					columnRefs.push(newCol)
				}
			}
			// REMOVE GLOBAL COLUMN TITLE ALIGNMENTS
			columnTitleStyle.set(Style.Attribute.ParagraphAlignment, null)

			// POPULATE THE OUTLINE WITH DATA
			tableData.forEach(dataRow => {
				tree.rootItem.addChild(null, item => {
					columnRefs.forEach((col, index) => {
						dataItem = dataRow[index]
						if(firstColumnIsRowHeaders && index === 0){
							item.setValueForColumn(dataItem, outlineColumn)
						} else {
							if(formatType !== 0){
								// REMOVE NON-NUMERIC CHARACTERS ($, €, £, etc.) EXCEPT "."
								dataItem = dataItem.replace(/[^\d\.]/g,"")
								dataItem = parseFloat(dataItem)
							}
							item.setValueForColumn(dataItem, col)
						}
					})
				})
			})

			utterance = createUtterance("Done!")
			synthesizer.speakUtterance(utterance)
		}
		catch(err){
			if(!causedByUserCancelling){
				utterance = createUtterance(err.message)
				synthesizer.speakUtterance(utterance)
				new Alert(err.name, err.message).show()
			}
		}
	});

	action.validate = function(selection, sender){
		return Pasteboard.general.hasStrings
	};

	return action;
})();