Office Script to copy data from one spreadsheet and add to a table

Question

I'm trying to set up a Power Automate flow using office scripts to transfer data from multiple spreadsheets to one master table. The flow is triggered by a file being put in a folder, then it's supposed to run two scripts, one to get the data from the new sheet, and another to transfer that data to the master spreadsheet. My issue is the latter script.

function main(workbook: ExcelScript.Workbook) : (number | string | boolean)[][] {

// Get the active worksheet
  let target_ws = workbook.getActiveWorksheet();

  // Get the range with the data
  let tbl_range = target_ws.getUsedRange()
    .getOffsetRange(6, 2)
    .getResizedRange(-6, -5);

  // Get date range
  let date_range_string = target_ws.getRange("H2").getValue().toString();
  let start_date = date_range_string.split(" - ")[0];
  let end_date = date_range_string.split(" - ")[1];

  // Get the data range
  let table_data = tbl_range.getValues();
  let rowsToMoveValues: (number | string | boolean)[][] = [];
  
  // Add the start date to each row
  if (start_date === end_date) {
    for (let i = 0; i < table_data.length; i++) {
      table_data[i][0] = start_date;
      rowsToMoveValues.push(table_data[i]);
    }
  }
  
  return rowsToMoveValues;
}

That gets the data, and it seems to be working fine.

function main(workbook: ExcelScript.Workbook, new_data: string[][])
{
  // Get the charity table
  let master_table = workbook.getTable('Master_Table');

  // Add new files to the table
  master_table.addRows(-1, new_data);
}

This one keeps failing, but it fails in a weird way. It's taking my output from the first script and converting it into one long string. "[["3/31/2022","",6957.16,75840],["3/31/2022",541,20.7,198],["3/31/2022",1306,1,113],...etc." instead of treating it as an array and adding the data in columns. Originally there were four columns in the master table, but that failed because the size of the input array didn't match. If I tell Power Automate to use result[0] it again treats it as a single string, but if I hand it just the result it displays like this:

[
  [
      [
        [
          "3/31/2022",
          "",
          6957.16,
          75840
        ],
        [
          "3/31/2022",
          541,
          20.7,
          198
        ],
        [
          "3/31/2022",
          1306,
          1,
          113
        ], etc.

I have a weird suspicion that there's one too many sets of brackets on the output, and I don't know why, and I don't know how to fix it. When I passed the result to the second function with 4 columns in the table, it failed. When I passed the result with only one column in the master table, it worked, but put everything on one line and in quotations.

Thanks for your help!

I would verify what you're getting in your second script for your new_data parameter. If you were to write the content to a cell in the master spreadsheet, what value do you get? You could do that by writing something like: `workbook.getActiveWorksheet().getRange("A1").setValue(JSON.stringify(new_data));` in the second script. You would just have to update A1 to some available cell in the master spreadsheet. — Brian Gonzalez, Apr 16 '22 at 17:52
Thanks for your response @BrianGonzalez! I did as you suggested and it output the single quote result: "[["3/31/2022","",6957.16,75840],["3/31/2022",541,20.7,198],["3/31/2022",1306,1,113],...etc." where it treats the array it shows in the second example but as a single string. So even when I pass it just "Result," it's trying to add one single cell of data that has the entire string. Is there a way to get typescript/excel to treat it as an array again? — CStevens, Apr 18 '22 at 19:15
I tried using the approach you listed with PowerAutomate but kept running into problems. In your first script, I would return a string in the function instead of an array. And I would use JSON.Stringify to convert the array to a string. In the second script I would accept a string as a parameter. And I would use JSON.Parse to convert the string back into an array. I wrote a post showing how to do that. You can see that post here: https://stackoverflow.com/questions/70463127/excel-online-workbook-links-linking-full-row-range/70488761#70488761 — Brian Gonzalez, Apr 18 '22 at 19:27

Office Script to copy data from one spreadsheet and add to a table

0 Answers0