Excel Script - How to output data to another workbook

Question

I want to make a script which outputs data in an external workbook in the same folder. The idea is to use the external workbook as a database and integrate it to Zapier and various other tools as I don't want to create a new Zap for every worksbook ( we have hundreds). Can't find a way to do that using Office Script (we're using Excel Online).

Here's my current script:

function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook.getWorksheet('Zapier'); //Here's the reference to the output sheet
    const AM = workbook.getActiveWorksheet().getRange('H13');
    const client = workbook.getActiveWorksheet().getRange('H14');
    const topupcurrency = workbook.getActiveWorksheet().getRange('H15');
    const topupwallet = workbook.getActiveWorksheet().getRange('H16');
    const grosstopup = workbook.getActiveWorksheet().getRange('H23');
    const fee = workbook.getActiveWorksheet().getRange('H22');
    const grossbudget = workbook.getActiveWorksheet().getRange('H21');
    const convfee = workbook.getActiveWorksheet().getRange('H20');
    const netbudget = workbook.getActiveWorksheet().getRange('H18');
  const data = [AM.getText(), client.getText(), topupcurrency.getText(), topupwallet.getText(), grosstopup.getText(), fee.getText(), grossbudget.getText(), convfee.getText(), netbudget.getText()]
    addRow(sheet, data);
    return;
}

function addRow(sheet: ExcelScript.Worksheet, data: (string | number | boolean)[]): void {

    const usedRange = sheet.getUsedRange();
    let startCell: ExcelScript.Range;
    // IF the sheet is empty, then use A1 as starting cell for update
    if (usedRange) {
        startCell = usedRange.getLastRow().getCell(0, 0).getOffsetRange(1, 0);
    } else {
        startCell = sheet.getRange('A1');
    }
    console.log(startCell.getAddress());
    const targetRange = startCell.getResizedRange(0, data.length - 1);
    targetRange.setValues([data]);
    return;
}

Tried to find a reference but only suggestions were to use Power Automate.

Answer 1

You can’t do it directly using Office Scripts, whoever told you to use PowerAutomate is correct.

This confirms it ... https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook

Ultimately, if you want to run it over 100’s of sheets, you need to make the script as generic as you can and then return a set of data that you can then pass to the central workbook.