Record day-to-day changes in Excel and report them with a Power Automate flow

Power Automate and Office Scripts combine to handle repetitive tasks for you. In this sample, you're tasked with recording a single numerical reading in a workbook every day and reporting the change since yesterday. You'll build a flow to get that reading, log it in the workbook, and report the change through an email.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Record and report daily readings

Add the following script to the sample workbook. In Excel, use Automate > New Script to paste the code and save the script. Save it as Record daily value and try the sample yourself!

function main(workbook: ExcelScript.Workbook, newData: string): string {
  // Get the table by its name.
  const table = workbook.getTable("ReadingTable");

  // Read the current last entry in the Reading column.
  const readingColumn = table.getColumnByName("Reading");
  const readingColumnValues = readingColumn.getRange().getValues();
  const previousValue = readingColumnValues[readingColumnValues.length - 1][0] as number;

  // Add a row with the date, new value, and a formula calculating the difference.
  const currentDate = new Date(Date.now()).toLocaleDateString();
  const newRow = [currentDate, newData, "=[@Reading]-OFFSET([@Reading],-1,0)"];
  table.addRow(-1, newRow);

  // Return the difference between the newData and the previous entry.
  const difference = Number.parseFloat(newData) - previousValue;
  console.log(difference);
  return difference.toString();
}

Sample flow: Report day-to-day changes

Follow these steps to build a Power Automate flow for the sample.

1. Create a new Scheduled cloud flow.

2. Schedule the flow to repeat every 1 Day.

   

3. Select Create.

4. In a real flow, you'll add a step that gets your data. The data can come from another workbook, a Teams adaptive card, or any other source. To test the sample, make a test number. Add an action and choose the Initialize variable action. Give it the following values.

   1. Name: Input
   2. Type: Integer
   3. Value: 190000

   

5. Add an action and choose the Excel Online (Business) connector's Run script action. Use the following values for the action.

   1. Location: OneDrive for Business
   2. Document Library: OneDrive
   3. File: daily-readings.xlsx (Chosen through the file browser)
   4. Script: Record daily value
   5. newData: Input (dynamic content)

   

6. The script returns the daily reading difference as dynamic content named "result". For the sample, you can email the information to yourself. Add an action and choose the Outlook connector's Send an email (V2) action (or whatever email client you prefer). Use the following values to complete the action.

   1. To: Your email address
   2. Subject: Daily reading change
   3. Body: "Difference from yesterday:" result (dynamic content from Excel)

   

7. Save the flow and try it out. Use the Test button on the flow editor page. Be sure to allow access when prompted.