top of page

PowerAutomate & Office Script2

Jun 16, 2025
2 min read

Since I had high expectations based on the previous content, I first pasted the values into Excel as the UI,

I pressed the button.


  1. Update the values in Excel or press the button.

  2. The Office Script is launched and updates the contents of Excel.

  3. Paste the updated content into an Outlook email and send it.

  4. The email body displays the headers in bold yellow with borders in a table format.


However, since this PowerAutomate is a cloud version, you will need to use the assigned OneDrive Excel workbook for Excel.


But I can also use files on my OneDrive. So I put the book on the file server and link it to the book on OneDrive. I can process the book on OneDrive with PowerAutomate. Here's an image of it.

Reference a specific cell in Excel on the cloud side to the same cell in the local workbook, and edit the referenced information so that it can be pasted into a separate sheet for use in the body of the email.

Well, here we go. It's easy to edit with VBA, but you can't edit it without OfficeScript. Unfortunately, I don't know OfficeScript, so I gave Copilot a simple VBA code and asked him to convert it into OfficeScript code. I also asked him to make the logic clearer and more readable by deleting the second line and after. And the code was completed.


function main(workbook: ExcelScript.Workbook) {

const wsModify = workbook.getWorksheet("modify");

const wsPut = workbook.getWorksheet("put");


modifyprocess // 1. Delete rows (from A2 onwards) until column A of the modify sheet is blank.

let row = 1;

while (true) {

const value = wsModify.getCell(row, 0).getValue();

if (value === "" || value === undefined) break;


const colCount = wsModify.getUsedRange().getColumnCount();

wsModify.getRangeByIndexes(row, 0, 1, colCount).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);

}


// 2. put → modify transcription (specify column number directly)

const putCols = [2, 4, 3, 5, 6, 7, 9, 15, 16, 17];

const modifyCols = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9];


for (let i = 0; i < putCols.length; i++) {

const row = 0;

const value = wsPut.getCell(row, putCols[i]).getValue();

wsModify.getCell(1, modifyCols[i]).setValue(value);

}


// 3. Date format setting (column H, column I)

const usedRange = wsModify.getUsedRange();

const lastRow = usedRange.getRowCount();

const dateFormat = "yyyy/mm/dd";

wsModify.getRangeByIndexes(1, 7, lastRow - 1, 1).setNumberFormatLocal(dateFormat);

wsModify.getRangeByIndexes(1, 8, lastRow - 1, 1).setNumberFormatLocal(dateFormat);


// 4. Fill A1 to J1 with yellow and bold

const headerRange = wsModify.getRange("A1:J1");

headerRange.getFormat().getFill().setColor("Yellow");

headerRange.getFormat().getFont().setBold(true);

}

When I asked Copilot to generate an Office Script, they provided me with the code much quicker than I expected. What's more, when I ran it in Excel on OneDrive, the data was properly formatted and output to a separate sheet!


To be honest, I thought, "This is practically finished."

At this point, I never dreamed that a ``cloud-based trap'' was waiting for me.


....Looking back, Copilot also mentioned "generating JSON files" and subsequent processing from the beginning.

But at the time, I was overconfident, thinking "I can just add that later, right?", and more than anything, I was happy that the script I had generated so easily worked properly.

As a result, it was as if I had stepped on a landmine myself.

 
 
 

Comments


bottom of page