Conversion of a Google Sheets macro to an OnlyOffice Sheets macro

Plugins
1

Hello Friends!
I would like to use a macro as the following Google Sheets macro but in the OnlyOffice Sheets macro. The problem is that I don’t know how to program in Java. I don’t know how to do the conversion. Can someone help me, please? I tried to study some OnlyOffice Sheets macros, but unfortunately I was not able to understand and convert the following Google Sheets macro to an OnlyOffice Sheets macro.

function CopyPaste()
{
var planilha = SpreadsheetApp.getActiveSpreadsheet();
var guiacoletora = planilha.getSheetByName(“Coletora”);
var guiareceptora = planilha.getSheetByName(“RelatorioDiario”);
var area = guiacoletora.getRange(“B4:F5”);
var linha = guiareceptora.getLastRow() + 1;
var colar = “A” + linha;
area.copyTo(guiareceptora.getRange(colar),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
guiacoletora.getRange(“B4:F5”).clear({contentOnly:true, skipFilteredRows:true});
guiacoletora.getRange(“B4”).activate();
Browser.msgBox(“Enviado com sucesso!”);
};

Basically the content from the interval “B4:F5” from the sheet “Coletora” will be pasted under the last filled line, or from the first unfilled line, onwards, in the sheet “RelatorioDiario”. After pasting the content from the interval “B4:F5”, I would want the macro to clean this interval from the sheet “Coletora”.
Thank you for your attention!

2

Hello @profesquinca

For that purpose you can use next methods:

  • GetSheet to get sheets of your spreadsheet;
  • Copy to copy data from one range to another;
  • Clear to clear out specified range.

In combination with simple JavaScript notation to find first empty cell in the column, an example of macro will look like:

var Coletora = Api.GetSheet("Coletora");
var RelatorioDiario = Api.GetSheet("RelatorioDiario");
var oRange1 = Coletora.GetRange("B4:F5");
var indexRowMax = 1048576;
var indexCol = 0;
var indexRow = 0;
for (; indexRow < indexRowMax; ++indexRow) {
    var oRange2 = RelatorioDiario.GetRangeByNumber(indexRow, indexCol);
    if ("" === oRange2.GetValue()) {
        break;
    }
}
oRange1.Copy(RelatorioDiario.GetRange(oRange2));
oRange1.Clear();

However, I don’t quite understand what this part of your original macro does:

Also, due to current technical limitations, you cannot call message boxes thus, I’m afraid, there is no way to convert this part of your original macro:

Note: I’d recommend trying out this macro on the backup copy of your original document because there may be certain differences in a way this macros works.

3

Thank you so much Constantine for you help! I will follow your instructions and after doing it I’ll give you a feedback.

4

Thank you so much Constantine! It worked perfectly here! You helped me a lot!

1 Like