cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chandra447
New Member

Create a new column in newly created excel file at one location and store it at other location

Hi Everyone,

I am new to power automate and creating flows.

Trying to achieve: 

Scenario: There are 2 locations location1(Where a team member uploads a dump) and location 2 where the uploaded file is modified and stored. (The 2 locations are in the same Sharepoint but they are different folders, I want to use location2 for creating power BI report.)

 

I want to automate creating a new column with uploaded date in an excel file which is uploaded at location1 and store the modified excel file at another location.

 

Any suggestions!

Thank you

Regards

Chandra

3 REPLIES 3
eliotcole
Skilled Sharer
Skilled Sharer

What have you tried so far, @chandra447 ?

 

Could you maybe show us a few screen shots of your flow so far? (blank out anything sensitive! 😅)

 

EDIT - Couple of things I can immediately recommend:

  1. Available Actions - Regarding the Excel for Business actions (not any API requests), you should need to be aware that there is only an action to do this in a table. So you'll first need to ensure that the excel files are formatted with tables.
  2. SharePoint - Is it possible that this data (if it's not just pure mathematics) would be better served in a SharePoint list? You could feed the list with Microsoft Forms or the built in equivalents. You could even generate spreadsheets for those folks that still need them.

automate.png

 

I have this flow, which I am using. But it fails when I use it. I am also attaching the script.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set range AW1:AW2 on selectedSheet
  selectedSheet.getRange("AW1:AW2").setFormulasLocal([["Date_dump"],["=DATE(20&LEFT(BC4,2),MID(BC4,3,2),MID(BC4,5,2))"]]);
  // Set range BC2:BC3 on selectedSheet
  selectedSheet.getRange("BC2:BC3").setFormulasLocal([["=CELL(\"filename\")"],["=MID(MID(BC2, FIND(\"[\",BC2),FIND(\"]\",BC2)- FIND(\"[\",BC2)-1),2,6)"]]);
}

automate.png

I have this flow, which I am using. But it fails when I use it. I am also attaching the script.

 

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set range AW1:AW2 on selectedSheet
  selectedSheet.getRange("AW1:AW2").setFormulasLocal([["Date_dump"],["=DATE(20&LEFT(BC4,2),MID(BC4,3,2),MID(BC4,5,2))"]]);
  // Set range BC2:BC3 on selectedSheet
  selectedSheet.getRange("BC2:BC3").setFormulasLocal([["=CELL(\"filename\")"],["=MID(MID(BC2, FIND(\"[\",BC2),FIND(\"]\",BC2)- FIND(\"[\",BC2)-1),2,6)"]]);
}

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,172)