Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

Fetch a calculated value from a single cell to another spreadsheet

(0) ShareShare
ReportReport
Posted on by 6

Hi,

 

New member here, I have so many questions on linking SharePoint, Excel Online and PowerAutomate. Here's the most urgent one:

 

Scenario:

1.) Subsidiary is using Excel Online for their P&L calculation, has a worksheet for P&L Summary that has calculated values and reference cells.

2.) Uploaded it to SharePoint for easier access and updating. 

3.) Values of specific cells in P&L Template in Excel Online needs to be collated into another sheet to serve as data dump.

4.) Data dump is the collation of all Daily P&L entries that will eventually turn to weekly / monthly.

5.) The condition we need to set in PowerAutomate, if selected or all values of P&L Summary sheet for this day was changed, will need to record the latest data for the day. If another was created tomorrow, a new row will be inserted in the data dump workbook.

6.) We have set 3 tables for this, Table 1 is for WBS dropdown and date, Table 2 is for the P&L Summary calculated values and Table 3 is the data dump. In total, 51 workbooks per unique WBS was set up to connect to one data dump (client request).

 

Problem:

Table 1 for WBS and Date has 2 columns, separated

Table 1 WBS.PNG

 

Table 2 for P&L Summary Computation upper part

Table 2 P&L 1.PNG

 

Table 2 for P&L Summary Computation lower part

Table 2 P&L 2.PNG

 

Table 3 data dump:

Data Dump.PNG

 

Specific cells to get is only values of:

a.) WBS

b.) Date

c.) Revenue

d.) Cost of Contract

e.) GM

f.) GM %

 

PowerAutomate only reads the values of Cost Column in Table 2.

Upon running, it duplicates entries even if there are no significant changes.

Can't fetch the data of cells horizontally since they are under Cost Column.

 

Any suggestion how to make this better or even work? Are there holes on our implem? e.g. not saving in OneDrive for Business. 

 

Can we get a specific cell values without using premium connectors?

If a row as defined in the flow, how to get a specific value from the summary?

Do we need to realign the summary values into columns?

How can we exactly get a cell value regardless of the table column? Any workarounds?

 

I will screencap the flow later or tomorrow for further reference.

 

Apologies for the long post.

 

Thanks.

 

JPA

  • jparroyo Profile Picture
    jparroyo 6 on at
    Re: Fetch a calculated value from a single cell to another spreadsheet

    Hi,

     

    Rather than using data append in PowerQuery, we just assigned a data fetch table within the individual sheets that links to the cell values that needs to be collated. From there, Flow picks up those values, Flow will provide time stamps and will run through all 66 sheets and dumped into a single Excel Online file.

     

    Thanks.

     

    JPA

  • jedunn Profile Picture
    jedunn 123 on at
    Re: Fetch a calculated value from a single cell to another spreadsheet

    Thanks for the response @jparroyo. What was the workaround you found in Flow?

  • jparroyo Profile Picture
    jparroyo 6 on at
    Re: Fetch a calculated value from a single cell to another spreadsheet

    Hi jedunn,

     

    Thanks for your suggestion. I will close this thread since we did a workaround in Flow. Maybe somehow in the future, your suggestion can help me on my other implems.

     

    JPA

  • jedunn Profile Picture
    jedunn 123 on at
    Re: Fetch a calculated value from a single cell to another spreadsheet

    @jparroyo I pulled together a solution with Office Scripts to extract and ingest data. I'm sure I'm making a few assumptions about your requirements. 

     

    Here's a picture of the flow. A simple recurrence trigger starts off the flow but you could define whatever works best for you. The first script runs to extract the data on a source workbook. The second script ingests this into a destination (data dump) workbook.

    jedunn_0-1639001893631.png

    Here is the first extraction script:

     

    // This script returns specific values from specified tables in the workbook
    function main(workbook: ExcelScript.Workbook): ExtractedData {
     // give the column indices names matching their expected content
     // you might make this solution more resilient to worksheet changes by using named ranges and the getNamedItem() method, alternatively searching for the content.
     const WBS_INDEX = 0;
     const DATE_INDEX = 1;
     const REVENUE_ROW_INDEX = 0;
     const COC_ROW_INDEX = 18;
     const GM_ROW_INDEX = 19;
    
     // get the ID and Date from table 1
     let idValues = workbook.getTable("Table1").getRangeBetweenHeaderAndTotal().getValues();
     
     // get the values from table 2
     let numberValues = workbook.getTable("Table2").getRangeBetweenHeaderAndTotal().getValues();
    
     const result:ExtractedData = {
     wbs: idValues[0][WBS_INDEX] as string,
     date: idValues[0][DATE_INDEX] as number,
     revenue: numberValues[REVENUE_ROW_INDEX][1] as number,
     coc: numberValues[COC_ROW_INDEX][1] as number,
     gm: numberValues[GM_ROW_INDEX][1] as number
     }
     console.log(result); // these should be viewable in the flow logs
     return result;
    } 
    // An interfact to pass the specific values from the tables through to a flow
    interface ExtractedData {
     wbs: string;
     date: number;
     revenue: number;
     coc: number;
     gm: number;
    }

     

     

    Here's the second ingestion script:

     

    /*
    Office Scripts will run only one main function.
    You can test this script in Excel for the web by commenting out this function, and uncommenting the next one
    */
    function main(workbook: ExcelScript.Workbook, extract: ExtractedData) {
     ingest(workbook, extract);
    }
    
    /*
    Office Scripts will run only one main function.
    // For testing in excel for the web invert which main function is commented-out
    function main(workbook: ExcelScript.Workbook) {
     const extract:ExtractedData = { wbs: "21-0015", date: 44355, revenue: 63048, coc: 36358.99, gm: 26689.01 };
     ingest(workbook,extract);
    }
    */
    
    // This script will ingest extracted data, if it finds a matching wbs number and date, the row is updated with new data. If no matching date and wbs number and date are found, a new row is added.
    function ingest(workbook: ExcelScript.Workbook, extract: ExtractedData)
    {
     // define the output or records table and where the wbs no. resides
     const records = workbook.getTable("Table1");
     // define the values for the new row to be added or updated
     const newRow = [
     extract.date,
     extract.wbs,
     extract.revenue,
     extract.coc,
     extract.gm
     ];
     
     // get the values from the records table
     let recordsData = records.getRangeBetweenHeaderAndTotal().getValues();
    
     // start with '-1' meaning no row is found
     let rowFound = -1;
     // in each row of the recordsData look for a row where the wbs and date match
     for (let row = 0; row < recordsData.length; row++) {
     if ((recordsData[row][1] == extract.wbs) && (recordsData[row][0] == extract.date)) {
     // return the first row found and break
     rowFound = row;
     break;
     }
     }
    
     let message = "";
     if (rowFound >= 0) {
     // when a row is found update the values
     // this assumes that as long as date and wbs numbers match you want to update the entire row
     // if there is no change in data we still update the values of the row
     records.getRangeBetweenHeaderAndTotal().getRow(rowFound).setValues([newRow]);
     message = "A row was updated";
    
     } else {
     // if there is no match, simply add a new row
     records.addRow(null/* add to end of table */,newRow);
     message = "A row was added";
     }
     // When run through Power Automate this message will appear in the outputs for the Run Script action.
     console.log(message)
    }
    // An interface to pass the extracted data through a flow.
    interface ExtractedData {
     wbs: string;
     date: number;
     revenue: number;
     coc: number;
     gm: number;
    }

     

     

    Source workbook looks like this:

    jedunn_1-1639002275524.png

     

    Destination workbook looks like this:

    jedunn_2-1639002298038.png

     

    If I understand your goals this pattern should get you what you want. Of course to make the solution more resilient to workbook changes you might avoid hard-coding the range locations of source data. Performance might be another consideration especially if tables get large. Using Office Scripts filter method to search/evaluate tables may help. I'm not a developer so I'm sure there multiple ways I'm missing on how to improve this. Hope this helps.

  • jedunn Profile Picture
    jedunn 123 on at
    Re: Fetch a calculated value from a single cell to another spreadsheet

    @jparroyo I would write this as an Office Script to get values from source workbook, pass those into a Flow, then use a second Office Script to ingest the values, compare the data to existing data, and add a new row if conditions are met. Have you used Office Scripts and the Run Script action? I'm happy to help point you to a few resources if this sounds feasible to you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard

Featured topics