Office Script - Split strings in vector & use dynamic cell address - Run an excel script from Power Automate

Question

I'm completely new on Office Script (with only old experience on Python and C++) and I'm trying to run a rather "simple" Office Script on excel from power automate. The goal is to fill specific cells (always the same, their position shouldn't change) on the excel file.
The power Automate part is working, the problem is managing to use the information sent to Excel, in excel.

The script take three variables from Power automate (all three strings) and should fill specific cells based on these. CMQ_Name: string to use as is.
Version: string to use as is.
PT_Name: String with names separated by a ";". The goal is to split it in as much string as needed (I'm stocking them in an Array) and write each name in cells on top of each other, always starting on the same position (cell A2).

I'm able to use CMQ_Names & Version and put them in the cell they're supposed to go in, I've already make it works.
However, I cannot make the last part (in bold above, part 2 in the code below) work.

Learning on this has been pretty frustrating as some elements seems to sometime works and sometimes not. Newbie me is probably having syntax issues more than anyting...

function main(workbook: ExcelScript.Workbook,
  CMQ_Name: string,
  Version: string,
  PT_Name: string ) 
{
// create reference for each sheet in the excel document
let NAMES = workbook.getWorksheet("CMQ_NAMES");
let TERMS = workbook.getWorksheet("CMQ_TERMS");

//------Part 1: Update entries in sheet CMQ_NAMES 
NAMES.getRange("A2").setValues(CMQ_Name);
NAMES.getRange("D2").setValues(Version);

//Update entries in sheet CMQ_TERMS 
TERMS.getRange("A2").setValues(CMQ_Name);

//-------Part 2: work with PT_Name

//Split PT_Name

let ARRAY1: string[] = PT_Name.split(";");
let CELL: string;
let B: string = "B"

for (var i = 0; i < ARRAY1.length; i++) {
 CELL = B.concat(i.toString());
 NAMES.getRange(CELL).setValues(ARRAY1[i]);
 }
}

I have several problems:

Some parts (basically anything with red are detected as a problem and I have no idea why. Some research indicated it could be false positive, other not. It's not the biggest problem either as it seems the code sometimes works despite these warnings.

Argument of type 'string' is not assignable to parameter of type '(string | number | boolean)[ ][ ]'.

I couldn't find a way to use a variable as address to select a specific cell to write in, which is preventing the for loop at the end from working. I've been bashing my head against this for a week now without solving it.

Could you kindly take a look?

Thank you!!

I tried several workarounds and other syntaxes without much success. Writing the first two strings in cells work, working with the third string doesn't.

EDIT: Thanks to the below comment, I managed to make it work:

function main(
  workbook: ExcelScript.Workbook,
  CMQ_Name: string,
  Version: string,
  PT_Name: string ) 
{
  // create reference for each table 
  let NAMES = workbook.getWorksheet("CMQ_NAMES");
  let TERMS = workbook.getWorksheet("CMQ_TERMS");

  //------Part 0: clear previous info
  TERMS.getRange("B2:B200").clear()

  //------Part 1: Update entries in sheet CMQ_NAMES 
  NAMES.getRange("A2").setValue(CMQ_Name);
  NAMES.getRange("D2").setValue(Version);

  //Update entries in sheet CMQ_TERMS 
  TERMS.getRange("A2").setValue(CMQ_Name);

  //-------Part 2: work with PT_Name
  //Split PT_Name
  let ARRAY1: string[] = PT_Name.split(";");
  let CELL: string;
  let B: string = "B"

  for (var i = 2; i < ARRAY1.length + 2; i++) {
    CELL = B.concat(i.toString());
    //console.log(CELL); //debugging
    
    TERMS.getRange(CELL).setValue(ARRAY1[i - 2]);
  }
}

For the array section, a loop is not needed. You should be able to write all of the values at once using setValues(). It expects the array in a certain structure depending on the range you're trying to write to. You can see more here: stackoverflow.com/a/69573251/16864869 — Brian Gonzalez
– Brian Gonzalez, Commented Jan 26, 2023 at 15:36

Skin · Accepted Answer · 2023-01-26 04:57:57Z

1

You're using setValues() (plural) which accepts a 2 dimensional array of values that contains the data for the given rows and columns.

You need to look at using setValue() instead as that takes a single argument of type any.

https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#excelscript-excelscript-range-setvalue-member(1)

As for using a variable to retrieve a single cell (or set of cells for that matter), you really just need to use the getRange() method to do that, this is a basic example ...

function main(workbook: ExcelScript.Workbook) {
    let cellAddress: string = "A4";
    let range: ExcelScript.Range = workbook.getWorksheet("Data").getRange(cellAddress);
    console.log(range.getAddress());
}

If you want to specify multiple ranges, just change cellAddress to something like this ... A4:C10

That method also accepts named ranges.

edited Jan 26, 2023 at 4:57

answered Jan 26, 2023 at 4:43

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StillLearning Over a year ago

Great thanks you so much, with your correction I was able to make it work.

Collectives™ on Stack Overflow

Office Script - Split strings in vector & use dynamic cell address - Run an excel script from Power Automate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related