Converting Google Spreadsheet App Script to Libre Office Macro

 I recently had a task to convert a small macro that did some data copying in a Google Spreadsheet, into an equivalent version in Libre Office.

Google has a very JavaScript like feel to it's scripting language, while Libre/Open Office uses either Python or Basic as their languages.  Basic seemed like a closer format to what I was coming from, and I've had some experience with VBA in the past, so I chose to go with that over the more powerful Python alternative.

My primary purpose in posting this is that there seems to be a lack of documentation around the Libre Office Basic language, which is understandable given the support of Python.  But in case someone else really feels like using Basic for a small project like I did, hopefully this will help them find some of the functions they need.

Here is the Google macro I was trying to convert

function fCopyData() {
  // get all the required sheets
  var shtSum = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  var shtFut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dest");

  // get the values we will be working with
  var vals = shtSum.getRange("A7:D45");
  var vIn = shtSum.getRange("I2:K10");

  // if we don't have enough rows, add more
  if((shtTran.getMaxRows()-shtTran.getLastRow()) < vals.getNumRows()) {
    shtTran.insertRows(shtTran.getMaxRows(),vals.getNumRows()-(shtTran.getMaxRows()-shtTran.getLastRow())+2);
  }

  if((shtFut.getMaxRows()-shtFut.getLastRow()) < vals.getNumRows()) {
    shtFut.insertRows(shtFut.getMaxRows(),vals.getNumRows()-(shtFut.getMaxRows()-shtFut.getLastRow())+2);
  }

  // populate dest data
  var today = new Date();
  today.setDate(today.getDate()+7);

  var x = 1;
  while(x <= vals.getNumRows()) {
    if(vals.getCell(x,1).getValue()=="ENV") {
      var lastRow = shtFut.getLastRow()+1;
      shtFut.getRange(lastRow,1).setValue(vals.getCell(x,4).getValue()*-1); // col 1
      shtFut.getRange(lastRow,2).setValue((today.getMonth()+1)+'/1/'+today.getYear()); // date
      shtFut.getRange(lastRow,3).setValue('=Year(B'+lastRow+')'); // year
      shtFut.getRange(lastRow,4).setValue('=Month(B'+lastRow+')'); // month
      shtFut.getRange(lastRow,5).setValue(vals.getCell(x,2).getValue()); // category
      shtFut.getRange(lastRow,6).setValue(vals.getCell(x,3).getValue()); // description
    }
    x++;
  }

  Browser.msgBox("Finished!");
}

Google has that special feature where you actually have to add new rows when you want to use them in their spreadsheets.  Moving to a desktop app no longer had that restriction, so part of the code could just be removed which was nice.

However, figuring out all the correction function names to take the place of the Google equivalents proved to be very difficult.

So here is the equivalent in Libre/Open Office Basic

REM  *****  BASIC  *****

Sub CopyData
  Dim Document As Object
  Dim Sheets As Object
  Document = ThisComponent  'assigns the current document to the variable document
  Sheets = Document.Sheets  'get the container of all Sheets

   ' get all the required sheets
  Dim shtTran As Object
  Dim shtSum As Object
  Dim shtFut As Object
  shtSum = Sheets.getByName("Source")
  shtFut = Sheets.getByName("Dest")

  ' get the values we will be working with
  Dim vals As Object
  Dim vIn As Object
  vals = shtSum.getCellRangebyName("A7:D45")
  vIn = shtSum.getCellRangebyName("I2:K10")

  ' populate dest data
  Dim thisYear As String
  Dim thisMonth As String
  Dim lastRow As Integer
  thisYear = Format(Now() + 7, "YYYY")
  thisMonth = Format(Now() + 7, "MM")

  oCursor= shtFut.createCursor
  oCursor.gotoEndOfUsedArea(False)
  lastRow = oCursor.RangeAddress.EndRow

  Dim Cell as Object
  Dim x As Integer
  For x = 0 To (vals.Rows.getCount() - 1)
    If vals.getCellByPosition(0,x).String = "ENV" Then
      lastRow = lastRow+1

      ' col 1
      Cell = shtFut.GetCellByPosition(0,lastRow)
      Cell.Value = vals.getCellByPosition(3,x).VALUE*-1
      Cell.NumberFormat = 125 ' secret code for Currency Format

      ' date
      Cell = shtFut.GetCellByPosition(1,lastRow)
      Cell.Value = DateValue(thisMonth & "/1/" & thisYear)
      Cell.NumberFormat = 36 ' secret code for Currency Format

      ' year
      Cell = shtFut.GetCellByPosition(2,lastRow)
      Cell.Formula = "=YEAR(B"+(lastRow+1)+")"
      Cell.NumberFormat = 0 ' Numeric Format

      ' month
      Cell = shtFut.GetCellByPosition(3,lastRow)
      Cell.Formula = "=Month(B"+(lastRow+1)+")"
      Cell.NumberFormat = 0 ' Numeric Format

      ' category
      Cell = shtFut.GetCellByPosition(4,lastRow)
      Cell.String = vals.getCellByPosition(1,x).String

      ' description
      Cell = shtFut.GetCellByPosition(5,lastRow)
      Cell.String = vals.getCellByPosition(2,x).String
    End If
  Next x

  msgbox "Finished!"
End Sub