Automated Script

Copper Contributor

Hi,
I've created an automated script in Excel to use on a Power Automate flow that inserts the values on my csv file skipping the first row into an excel.
I now want to change the script so that whenever the value in my first column changes it adds an empty row in my excel, so the different values are organized in different groups. But I am having troubles in achieving this.
I am attaching both my current script and my attempt to achieve my requirement.

The second script is prompting my an error message like this: "Error Office JS: Line 42: Range setValues: The number of rows or columns in the entry matrix does not correspond to the size or dimensions of the interval".
Can you help me fix this?
Thank you

 

Current code:

function main(workbook: ExcelScript.Workbook, csv2: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters from csv files.
  csv2 = csv2.replace(/\r/g, "");

  // Split each line into a row for csv1.
  let rows2 = csv2.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g

if (rows2.length > 1) {
    let data2: string[][] = [];
    rows2.forEach((value, index) => {
      if (index > 0 && value.length > 0) { // Skip the first row
        let row2 = value.match(csvMatchRegex);
        // Check for blanks at the start of the row.
        if (row2[0].charAt(0) === ',') {
          row2.unshift("");
        }
        // Remove the preceding comma.
        row2.forEach((cell, index) => {
          row2[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
        });
        data2.push(row2);
      }
    });
    // Put the data in the worksheet starting from cell B10.
    let range2 = sheet.getRangeByIndexes(9, 1, data2.length, data2[0].length);
    range2.setValues(data2);
  }
}

 New code:

function main(workbook: ExcelScript.Workbook, csv2: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  // Remove any Windows \r characters from csv files.
  csv2 = csv2.replace(/\r/g, "");

  // Split each line into a row for csv1.
  let rows2 = csv2.split("\n");

  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g

if (rows2.length > 1) {
    let data2: string[][] = [];
    rows2.forEach((value, index) => {
      if (index > 0 && value.length > 0) { // Skip the first row
        let row2 = value.match(csvMatchRegex);
        // Check for blanks at the start of the row.
        if (row2[0].charAt(0) === ',') {
          row2.unshift("");
        }
        // Remove the preceding comma.
        row2.forEach((cell, index) => {
          row2[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
        });
        if (row2[0] !== currentCompany) {
          // Insert an empty row if the company has changed
          if (index !== 0) {
            data2.push(['']);
          }
          currentCompany = row2[0];
        }
        data2.push(row2);
      }
    });
    // Put the data in the worksheet starting from cell B10.
    let range2 = sheet.getRangeByIndexes(9, 1, data2.length, data2[0].length);
    range2.setValues(data2);
  }
}


 

1 Reply

A Microsoft 365 commercial or EDU plan?

I have no this version office.

 

data2.push(['']);

line 33

i guess line 33 need push same dimension values to array

like 

data2.push(['','','']) //if csv has 3 columns

 

 

I test this under nodejs.

take for your reference

 

 

const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
let csv2=`a,b,c
a1,b1,c1
a1,b1,c1
a2,b2,c2
a3,b2,c2
a4,b2,c2
a4,b2,c2
a5,b2,c2
`
// Remove any Windows \r characters from csv files.
  csv2 = csv2.replace(/\r/g, "");
  // Split each line into a row for csv1.
  let rows2 = csv2.split("\n");

//console.log(rows2)
let data2= [];
var lastCompany
function sameOfLastCompany(index,row2){
	if(index==1){
lastCompany=row2[0]
		return 1
	}
if (row2[0]!== lastCompany){
lastCompany=row2[0]
return 0
	}
return 1
}
function getBlankArr(arrRow2){
return arrRow2.fill(' ')
}
var arrEmpty=new Array()
//console.log(rows2[0])
arrEmpty=getBlankArr(rows2[0].split(','))
//console.log(arrEmpty);
if (rows2.length > 1) {
	rows2.forEach((value, index) => {
      if (index > 0 && value.length > 0) { // Skip the first row
        let row2 = value.match(csvMatchRegex);
        // Check for blanks at the start of the row.
        if (row2[0].charAt(0) === ',') {
          row2.unshift("");
        }
        // Remove the preceding comma.
        row2.forEach((cell, index) => {
          row2[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
        });

//console.log(row2)

if(sameOfLastCompany(index,row2)==0){

        data2.push(arrEmpty);
        data2.push(row2);
}else{
        data2.push(row2);

}
      }
    });
}

    // Put the data in the worksheet starting from cell B10.
//    let range2 = sheet.getRangeByIndexes(9, 1, data2.length, data2[0].length);
  //  range2.setValues(data2);

console.log(data2)