Spreadsheet macro to request cell values-prompt() not working

Docs API
1

I’m a new OnlyOffice user (previously Excel) and I’m trying to write a macro which prompts the user to provide values for several cells.

The prompt() command doesn’t work-nothing pops up to request input values.

If I manually add the values in the macro it updaates ok, but the values will differ each time.

If I can get this to work I would like to insert a new row each time then copy/paste the totals row to a summary sheet.

No luck so far.

Thanks for any help-I have never used JS before.

2

Hi @kevinr, welcome.

prompt() isn’t available in ONLYOFFICE macros. Since v7.1 macros run in strict mode, so access to window, document, and alert is restricted — and prompt() / confirm() fall under the same limitation. See Getting started.

Workaround: put the inputs in fixed cells and read them from the macro:

js

(function () {
    let sheet = Api.GetActiveSheet();
    let amount   = sheet.GetRange("B1").GetValue();
    let category = sheet.GetRange("B2").GetValue();
    sheet.GetRange("D1").SetValue(amount);
    sheet.GetRange("D2").SetValue(category);
})();

If you need a real input dialog, that requires a plugin rather than a macro.

3

Hi,

Thanks for your reply.

So is there no easy way to write a macro to select cell A1, ask the user to input a value for that cell, then jump to cell B1 and ask for a value, etc?

I am migrating from excel and know no JavaScript.

If this cannot be achieved fairly easily, I will need to return to excel.

I am too old to start learning programming languages, but I am keen to move away from Microsoft products.

Any help or suggestions would be appreciated.

Thanks

.

4

Hi @kevinr,

For that exact workflow, unfortunately there isn’t an InputBox-style function in ONLYOFFICE macros, so a macro can’t stop at A1, wait for input, then continue to B1, etc. A proper input dialog would require a plugin.

A simpler workaround is to enter the values directly in the row and use the macro for the repetitive part:

(function () {
    let sheet = Api.GetActiveSheet();
    sheet.GetRows("2").Insert("down");
    sheet.GetRange("A2").Select();
})();

So the flow would be: enter values → run the macro → a new row is added and A2 is selected.

If you tell me where your totals are and where they should go on the Summary sheet, I can add that part too.

5

Hi @Nurullo,

Thanks for your additional reply.

On the ‘summary’ sheet I need to select the top row of data (below any column titles) and insert a new row above this.

This allows entries to be copied in reverse chronological order on the ‘summary’ sheet, so that the most recent entries appear on the top row.

The cell values from ‘sheet1’ would be copied to the new top row of the ‘summary’ sheet, then cleared from ‘sheet 1’ ready for new input.

I hope this makes sense?

6

Hi @kevinr, that makes sense.

This macro inserts a new row at the top of Summary, copies the values from Sheet1, and clears Sheet1 for the next entry:

js

(function () {
    let source  = Api.GetSheet("Sheet1");
    let summary = Api.GetSheet("Summary");

    // Insert a blank row at the top of Summary (below the titles in row 1)
    summary.GetRange("A2:D2").Insert("down");

    // Copy the values from Sheet1 to the new top row
    summary.GetRange("A2").SetValue(source.GetRange("A2").GetValue());
    summary.GetRange("B2").SetValue(source.GetRange("B2").GetValue());
    summary.GetRange("C2").SetValue(source.GetRange("C2").GetValue());
    summary.GetRange("D2").SetValue(source.GetRange("D2").GetValue());

    // Clear Sheet1 for the next entry
    source.GetRange("A2:D2").Clear();
})();

Type your values on Sheet1, run the macro, and they move to a new top row on Summary.

Just make sure the sheet names match yours exactly (case matters), and adjust the columns if you use more or fewer than A–D.

7

Hi @Nurullo,

This code worked great (with the minor adjustments you suggested).

I will assign this to a button once I’ve fine tuned it further.

I feel much happier to persevere with OnlyOffice now that I’m making progress.

Thanks you for your time and patience.

8

I noticed that the formatting of the copied range has not carried over to the Summary sheet when pasted.

Any suggestions-I looked for solutions online without success.

Thanks

9

Hi @kevinr,

That happens because SetValue copies only the value. Use Copy instead — it brings the formatting across too:

js

(function () {
    let source  = Api.GetSheet("Sheet1");
    let summary = Api.GetSheet("Summary");

    // Insert a blank row at the top of Summary
    summary.GetRange("A2:D2").Insert("down");

    // Copy the entry from Sheet1 (values + formatting)
    source.GetRange("A2:D2").Copy(summary.GetRange("A2:D2"));

    // Clear Sheet1 for the next entry
    source.GetRange("A2:D2").Clear();
})();

Just the one Copy line replaces the four SetValue lines.