Filling an Array with more than 8 values filles everything with the 9. ( Office Script / Excel Online )

Question

this question relates to Office Script which uses TypeScript and I don't know if its an issues of my none existant coding skills or the Office Skript System.

Basically pushing 8 variables to an array works, if I add a 9. the array get's overwritten with it.

Does anybody know what I'm doing wrong?

Thanks in advance!

function main(Workbook: ExcelScript.Workbook): InterviewInvite[] {
let selectedSheet = Workbook.getWorksheet("Tabelle1");
// Convert the table rows into InterviewInvite objects for the flow.
let invites: InterviewInvite[] = [];
  invites.push({
A: selectedSheet.getRange("A1").getValue().toString(),
B: selectedSheet.getRange("A2").getValue().toString(),
C: selectedSheet.getRange("A3").getValue().toString(),
D: selectedSheet.getRange("A4").getValue().toString(),
E: selectedSheet.getRange("A5").getValue().toString(),
F: selectedSheet.getRange("A6").getValue().toString(),
G: selectedSheet.getRange("A7").getValue().toString(),
H: selectedSheet.getRange("A8").getValue().toString(),
I: selectedSheet.getRange("A9").getValue().toString(),
  });
  console.log(JSON.stringify(invites));
return invites;
}
// The interview invite information.
interface InterviewInvite {
A: string
B: string
C: string
D: string
E: string
F: string
G: string
H: string
I: string
}

NOK

OK

Thanks Christoph for reporting this. This looks like a bug in Office Scripts, your code looks sound. We are investigating the issue and will let you know when we have a fix. Hopefully, the workaround suggested by @YutaoHuang will work for you. — Jay Rathi - Microsoft, Jul 09 '21 at 20:18

score 0 · Answer 1 · answered Jul 09 '21 at 08:31

function main(Workbook: ExcelScript.Workbook): InterviewInvite[] {
let selectedSheet = Workbook.getWorksheet("Tabelle1");
// Convert the table rows into InterviewInvite objects for the flow.
let invites: InterviewInvite[] = [];
  invites.push({
A: selectedSheet.getRange("A1").getValue().toString(),
B: selectedSheet.getRange("A2").getValue().toString(),
C: selectedSheet.getRange("A3").getValue().toString(),
D: selectedSheet.getRange("A4").getValue().toString(),
E: selectedSheet.getRange("A5").getValue().toString(),
F: selectedSheet.getRange("A6").getValue().toString(),
G: selectedSheet.getRange("A7").getValue().toString(),
H: selectedSheet.getRange("A8").getValue().toString(),
I: selectedSheet.getRange("A9").getValue().toString(),
  });
  console.log(JSON.stringify(invites));
return invites;
}

function main pushes only one element to invites array.

Consider next example:

const invites = []

invites.push({
  A: 1,
  B: 1,
  C: 1,
  D: 1,
  E: 1,
  F: 1,
  G: 1,
  H: 1,
  I: 1,
});

invites // [{A: 1, B: 1, C: 1, D: 1, E: 1, …}]

invites.length // 1

If you want to push another element, just call push again:

invites.push(2)

Basically pushing 8 variables to an array works, You did not push 8 elements to the array

score 0 · Answer 2 · answered Jul 09 '21 at 19:28

Updating the original code to this worked for me:

function main(Workbook: ExcelScript.Workbook): InterviewInvite[] {
  let selectedSheet = Workbook.getWorksheet("Sheet1");
  // Convert the table rows into InterviewInvite objects for the flow.
  let invites: InterviewInvite[] = [];
  let values = selectedSheet.getRange("A1:A9").getValues();
  invites.push({
      A: values[0][0].toString(),
      B: values[1][0].toString(),
      C: values[2][0].toString(),
      D: values[3][0].toString(),
      E: values[4][0].toString(),
      F: values[5][0].toString(),
      G: values[6][0].toString(),
      H: values[7][0].toString(),
      I: values[8][0].toString(),
  });
  console.log(JSON.stringify(invites));
  return invites;
}


// The interview invite information.
interface InterviewInvite {
  A: string
  B: string
  C: string
  D: string
  E: string
  F: string
  G: string
  H: string
  I: string
}

The main difference is this line, which reads all the values from the range of A1:A9 into a two dimensional array in one shot:

  let values = selectedSheet.getRange("A1:A9").getValues();

Also I believe this would be more efficient vs. making several getRange(...).getValue() calls for each individual cell.

I'm not sure yet why the original code wouldn't work. That might be a bug.

Filling an Array with more than 8 values filles everything with the 9. ( Office Script / Excel Online )

2 Answers2