CODEWITHSUNDEEP
excelms-office-script
mean_
mean_

Reputation: 3

Set Array Values to Range as Text in Office Scripts

I am trying to copy data from one workbook to another using a combination of Office Scripts and Power Automate. Using office scripts, I was able to get the range text to an array. However, when setting the array data to the destination workbook, the leading zeroes are being removed. Is there a way where I can set the destination range values as text as well, so there leading zeroes are retained?

Below is the script I am using to set the values to the destination workbook:

function main(workbook: ExcelScript.Workbook, wsName: string,
    startCell: string, strArr: string) {

    //Convert the strArr to an array
    let dataArr: string[][] = JSON.parse(strArr)

    //Declare and assign the worksheet
    let ws = workbook.getWorksheet(wsName);

    //Get the old data
    //let dataArr = ws.getRange(startCell).getSurroundingRegion().
    //    getValues()

    //Join the dataArr and newDataArr
    //dataArr = dataArr.concat(newDataArr)

    //Get the size of the range to paste to
    let dataRng = ws.getRange(startCell).
        getAbsoluteResizedRange(dataArr.length, dataArr[0].length)

    //Set the values of the new data set
    dataRng.setValues(dataArr)

}

Upvotes: 0

Views: 225

Answers (1)

mean_
mean_

Reputation: 3

I was able to solve the issue using below script. I added a line that changes the range format to text:

function main(workbook: ExcelScript.Workbook, wsName: string,
startCell: string, strArr: string) {

//Convert the strArr to an array
let dataArr: string[][] = JSON.parse(strArr)

//Declare and assign the worksheet
let ws = workbook.getWorksheet(wsName);

//Delete existing data
ws.getRange('A:G').clear();

//Get the size of the range to paste to
let dataRng = ws.getRange(startCell).
    getAbsoluteResizedRange(dataArr.length, dataArr[0].length)

//Set the formatting to text
dataRng.setNumberFormat('@');

//Set the values of the new data set
dataRng.setValues(dataArr)

}

Upvotes: 0

Related Questions