Add buttons to do simple things

[utoddl]

I feel like I should be able to figure this out. Clearly there’s lots of capabilities within Calc that have eluded me. But every time I’ve tried (and there have been several attempts by now), I’ve ended up out of time, lost, confused, frustrated, and ultimately unsuccessful.

A. I have a spreadsheet in which, every day, I insert a new row between existing rows 1 and 2, and insert today’s date into column 1 of the new row. This is neither hard nor inconvenient. But I’d like to add a button that would do that with a single click.

B. After adding today’s data and saving (in .fods format, because this is going into a git repo), I then do a File » Save a Copy…, and in the “Save a Copy” dialog I select File type: Text CSV (.csv) and [Save]. Again, neither hard nor inconvenient, but one of those things that’s just tedious enough that a one-click button could make even simpler — if only I could make sense of how to pull it off.

I would very much appreciate an outline of how to accomplish such things. Bonus points for documentation links for background on each part of such an outline. Explicit steps are welcome, too, of course, but I really want to learn how these things can be built so I can do them myself without bothering the fine folks here. Thanks.

[Villeroy]

Some kind of database can do most of that with minimal effort. A spreadsheet is not a database.

[Lupp]

Single-click actions of a specialised kind like the examples you describe will mostly (next to always) have to run user code.
In your cases recorded macros can possibly do the job.
Depending on the special case it may be better to assign the call to a toolbar element instead of a button (form control).

[utoddl]

Thanks, @Lupp . I’ve now figured out how to enable the macro recorder, and I’ve recorded a macro which invokes dispatcher.executeDispatch(document, ".uno:SaveACopy", "", 0, Array()). It didn’t record the filter options, which seems to be what would be in place of the empty Array(). I can probably dig out what that’s supposed to look like, now that I’ve got my nose under the tent, so to speak.

I’m intrigued by the idea of a custom toolbar element. Didn’t know that was A Thing™, so, something to search around for.

So that’s at least three good-to-know-about, high-level things, one in each of your sentences. What have I not been finding from which I could glean that kind of knowledge?

There appears to be two flavors of macros: Basic and Python. And my recorded macro seems to be Basic. Do you have any insights into which flavor to pursue? I’m assuming Basic is “old school” while Python is the “new hotness”, but maybe I’m projecting.

[Villeroy]

Your macro saves Open Document Format without any filter options.
Record that macro again.
Call the save-as dialog, check “Edit filter settings”, file type “Text (csv) .csv)”.
Now the macro looks like this

sub rec_save_csv
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///tmp/test/Untitled.csv"
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "59,39,ANSI,1,,0,false,true,false,false,false,0,true,false"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
end sub

[Lupp]

The kind of automation you want always requires some knowledge of custom code and the necessary settings.
Recorded macros may also need to be reworked.
If you don’t want to deal with this in detail, you should drop the “project”.
You certainly don’t need a full featured programming language like Python, because the actual work is done by the LibreOffice API, and you only need a bridge to use it.
Basic is well suited for this, and is maintained by the developers.

See attached example:
disask118553.ods (24.4 KB)

Storing the csv version is not contained in the example.
There is a help page about csv export.

[joshua4]

For B: This might be a little rough, since it is ripped out of a bigger scenario, but it should have the components for a fully functional approach. Notice that the save path is in a Named Range location that is global (not sheet) for the workbook. The pieces are from so many sources I can’t really credit all of them.

Sub ExportCsvSheet(Filename As String, ExportSheet As Object, Extension As String, Delimiter As String)
	Dim PathSeparator As String
	Dim FileDirectory As String
	Dim FullFilename As String
	Dim FileURL As String
		
	PathSeparator = "/"
	
	FileDirectory = CurrentOutputPath(PathSeparator)
    Dim Propval(1) As New com.sun.star.beans.PropertyValue
    Propval(0).Name = "FilterName"
    Propval(0).Value = "Text - txt - csv (StarCalc)"
    Propval(1).Name = "FilterOptions"
    REM Export seettings
    REM 	44 means comma, 9 means tab, 34 means double quote
    REM 	ASCII in position 1 (Delimiter) is the separation delimiter
    REM 	ASCII in position 2 is the quote character for text
    REM 	true/false in position 7 to determine if all text is quoted
    REM 	true/false in position 8 to determine if all numbers are considered text and quoted
    Propval(1).Value = ASC(Delimiter) & ",34,0,1,1,,false"

	ThisComponent.getCurrentController.setActiveSheet(ExportSheet)
	FullFilename = FileDirectory & Filename & "." & Extension
	FileURL = convertToURL(FullFilename)
	On Error Goto Handler
		ThisComponent.StoreToURL(FileURL, Propval())
	On Error Goto 0
	Exit Sub
	
	Handler:
		MsgBox "File already exists. Normally would be overwritten. Is it open in another window?" & Chr$(13) & Error$ & " at line " & Erl
	
End Sub

REM Use the global string tools library to generate a path from main document path
Function DocPath(PathSeparator As String)
	GlobalScope.BasicLibraries.loadLibrary("Tools")
	DocPath = Tools.Strings.DirectoryNameoutofPath(ThisComponent.getURL(), PathSeparator) & PathSeparator
End Function

REM 
Function CurrentOutputPath(PathSeparator As String) As String
	CurrentOutputPath =  GlobalScalar("OutputFolder")
	If Right(CurrentOutputPath, 1) <> PathSeparator Then
		REM Then assume no ending path separator so add one
		CurrentOutputPath = CurrentOutputPath & PathSeparator
	EndIf
End Function

REM Same as just =RangeName in a cell
REM 	where RangeName is a global range
REM Return a Variant value from a single cell (top left or only cell) in range RangeName
Function GlobalScalar(RangeName) As Variant
	GlobalScalar = ThisComponent.NamedRanges.getByName(RangeName).ReferredCells.getCellByPosition(0,0).DataArray(0)(0)
End Function

[utoddl]

Huge thanks to both @Lupp and @joshua4 . I can only mark one as a solution, but all these responses are extremely valuable, at least to me. I very much appreciate the time spent and the insights shared. Cheers to all.