VCOO0039

OmniOutliner: Import Copied Spreadsheet

A specialized command for importing tab-return-delimited spreadsheet data from the clipboard.

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.

Scope and Syntax

Language: English (United States 🇺🇸 · Great Britain 🇬🇧 · Australia 🇦🇺 · Canada 🇨🇦)

Scope: OmniOutliner

Command phrase variations (words in [brackets] are optional):

• “Import Copied Spreadsheet”

Requirements

• OmniOutliner: 5.10 (macOS), OmniOutliner 3.9.1 (iOS/iPadOS)
• System: macOS 12, iOS 15, iPadOS 15

Video: Import Copied Spreadsheet

A specialized command for importing tab-return-delimited spreadsheet data from the clipboard.

PLAY (E) PAUSE BACK REWIND

Video: Import Copied Spreadsheet

A specialized command for importing tab-return-delimited spreadsheet data from the clipboard.

PLAY (E) PAUSE BACK REWIND

Downloads

IMPORTANT: On iPad and iPhone, download and install the provided OmniOutliner plug-in, which is called by the installed Shortcut action.

• ⇩ COMMANDS-FILE (OmniOutliner 5) (macOS)
• ⇩ SHORTCUT-LINK (“Import Copied Spreadsheet”) (iOS/iPadOS)
• ⇩ PLUG-IN FILE (iOS/iPadOS)

Script Code

Here is the script code.

Import Copied Spreadsheet
Copy

(async () => {
	try {
		function createUtterance(textToSpeak){
			AlexID = (
				(app.platformName === "macOS") ? 
				"com.apple.speech.synthesis.voice.Alex" : 
				"com.apple.speech.voice.Alex"
			)
			voiceObj = Speech.Voice.withIdentifier(AlexID)
			voiceRate = 0.4
			utterance = new Speech.Utterance(textToSpeak)
			utterance.voice = voiceObj
			utterance.rate = voiceRate
			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
		if(!Pasteboard.general.hasStrings){
			throw {
				name: "Missing Data",
				message: "There is no textual data on the clipboard."
			}
		}
		
		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()
		}
	}
})();