Copy Excel cellvalues to sharepoint list via Office script and Power Automate

(1) Share

Report

Posted on by Luessem

Hi,

I am stuck in a problem, hope for help:

Usecase: Values of 9 cells in daily updated Excel-file have to be copied to sharepoint list

Excel-File:

Office-Script (IVS.osts):

function main(workbook: ExcelScript.Workbook):MyData {

 let ivs = workbook.getWorksheet("3_Annuittendarlehen");

 return {
 timecode: String(ivs.getRange("A18").getText()),
 twoYears: String(ivs.getRange("B5").getText()),
 threeYears: String(ivs.getRange("C6").getText()),
 fourYears: String(ivs.getRange("D7").getText()),
 fiveYears: String(ivs.getRange("E8").getText()),
 sixYears: String(ivs.getRange("F9").getText()),
 sevenYears: String(ivs.getRange("G10").getText()),
 eightYears: String(ivs.getRange("H11").getText()),
 nineYears: String(ivs.getRange("I12").getText()),
 tenYears: String(ivs.getRange("J13").getText()),
 } 
}

interface MyData {
 timecode: string;
 twoYears: string;
 threeYears: string;
 fourYears: string;
 fiveYears: string;
 sixYears: string;
 sevenYears: string;
 eightYears: string;
 nineYears: string;
 tenYears: string;
}

Extract of my flow, triggered by new uploaded file on sharepoint:

My problem:

Response only returns 1 value over and over again????

Any ideas?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Sort by

trice602 16,239 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @Luessem ,

This Office Script looks good; please try this one.

function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); let ColumnA = getLastValueInColumn(sheet, "A"); let ColumnB = getLastValueInColumn(sheet, "B"); let ColumnC = getLastValueInColumn(sheet, "C"); let ColumnD = getLastValueInColumn(sheet, "D"); let ColumnE = getLastValueInColumn(sheet, "E"); let ColumnF = getLastValueInColumn(sheet, "F"); let ColumnG = getLastValueInColumn(sheet, "G"); let ColumnH = getLastValueInColumn(sheet, "H"); let ColumnI = getLastValueInColumn(sheet, "I"); let ColumnJ = getLastValueInColumn(sheet, "J"); console.log(`ColumnA: ${ColumnA}`); console.log(`ColumnB: ${ColumnB}`); console.log(`ColumnC: ${ColumnC}`); console.log(`ColumnD: ${ColumnD}`); console.log(`ColumnE: ${ColumnE}`); console.log(`ColumnF: ${ColumnF}`); console.log(`ColumnG: ${ColumnG}`); console.log(`ColumnH: ${ColumnH}`); console.log(`ColumnI: ${ColumnI}`); console.log(`ColumnJ: ${ColumnJ}`); } function getLastValueInColumn(sheet: ExcelScript.Worksheet, columnLetter: string): string | undefined { let column = sheet.getRange(`${columnLetter}:${columnLetter}`); let values = column.getValues(); for (let i = values.length - 1; i >= 0; i--) { if (values[i][0] !== "") { return values[i][0]; } } return undefined; }

Was this reply helpful? Yes No
Luessem 17 on at

Like (0)

Report
Copy link

Link copied!

Hello @trice602 ,

thank you for your post! Would like to test the new script, but...

Was this reply helpful? Yes No
Luessem 17 on at

Like (0)

Report
Copy link

Link copied!

...in addition to my last post:

I tested around and I saw, that the getRange-functions returns an object, but on this object the getValues- or getTexts-methods are not working.

Further more I do not really understand why you want to iterate backwards through the columns to find the values. I just need the values of the yellow-marked cells in the screenshot, so hard coded adresses should be fine. But why does my flow receive always the same value and not the values of the yellow-marked cells?

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Luessem,

I tested out your script on the sample data you shared and it seemed to work fine. Some questions to try to narrow down what could be going wrong:
Where in your worksheet does the value "3,597681917" appear?
Does the script output the correct values if you run it from the Excel client (instead of in Power Automate)? You can check this by logging the object before you return it:

function main(workbook: ExcelScript.Workbook): MyData { let ivs = workbook.getWorksheet("3_Annuittendarlehen"); let data: MyData = { timecode: String(ivs.getRange("A18").getText()), twoYears: String(ivs.getRange("B5").getText()), threeYears: String(ivs.getRange("C6").getText()), fourYears: String(ivs.getRange("D7").getText()), fiveYears: String(ivs.getRange("E8").getText()), sixYears: String(ivs.getRange("F9").getText()), sevenYears: String(ivs.getRange("G10").getText()), eightYears: String(ivs.getRange("H11").getText()), nineYears: String(ivs.getRange("I12").getText()), tenYears: String(ivs.getRange("J13").getText()), } console.log(data); return data; }

Was this reply helpful? Yes No