Incrementing Alphabet Counter for Excel Online in Office Script-CodePudding

Context We have data that is exported from elsewhere that we need to process on a regular basis automatically, but we've no control over how it is exported and it comes out in an unsightly and difficult to use manner. So we automated a script which takes only what we need from this first spreadsheet (oldWorksheet) and place it into a new one (newWorksheet)

We have working code, as it was auto-generated when we performed the actions manually, but we would like to try and reduce the lines of repetitive code required with some iterative loops. The pseudocode seemed promising, but we realise now that we're not sure how to go about incrementing some of these variables in the way that we want.

Question We want to have the relevant variables increase by a pre-decided amount of characters in alphabetical order every time it loops. How would we go about doing this?

var i:number = 0;
var x:string = "B";
var y:string = "D";

while (i < 100)
{
    newWorksheet.getRange(x:x).copyFrom(oldWorksheet.getRange(y:y), ExcelScript.RangeCopyType.all, false, false);
    // increment x by 1 (A -> B -> C etc.)
    // increment y by 2 (A -> C -> E etc.)
    i  ;
}

Variables x and y are pertaining to the rows/columns of the Excel spreadsheets, and they will go past Z and on towards AA, AB, AC etc.

Would this be possible to do within the same process? Or will we have to keep track of how far it has gotten and then concatenate two variables together whilst it's being read by the script? i.e. something to the effect of:

while (i < 100)
{
    z = columnTracker   x    
    // z = A   D (= AD)
    newWorksheet.getRange(z:z)...

Thanks in advance!

CodePudding user response：

I believe this code does what you want. Please see below:

function main(workbook: ExcelScript.Workbook) {
    let i: number = 0;
    let counter: number = 0;

    let oldWorksheet : ExcelScript.Worksheet = workbook.getWorksheet("oldWorksheet");
    let newWorksheet: ExcelScript.Worksheet = workbook.getWorksheet("newWorksheet");

    while (i < 100 ){
        let oldRange: ExcelScript.Range = oldWorksheet.getCell(0,counter).getEntireColumn();
        let newRange: ExcelScript.Range = newWorksheet.getCell(0,i).getEntireColumn();
        newRange.copyFrom(oldRange);
        // increment x by 1 (A -> B -> C etc.)
        // increment y by 2 (A -> C -> E etc.)
        i  ;
        counter  = 2;
    }
}

In the loop, this code uses the counter variable with getCell() for the old range, and the i variable with getCell() for the new range. Once we have the cell, we can use getEntireColumn() to get the range for the whole column. After the range from the old sheet is copied to the new sheet, the counter variable is incremented by two, and it goes to the next iteration of the loop.

While this code works, it's likely slow since you're doing a lot of copying and pasting. If your data is normalized, you may be able to work with arrays. If you iterate through the array of values from the old sheet, create a new array, and write the new array to the new sheet using SetValues, the resulting operation should be much faster. That code may look something like this:

function main(workbook: ExcelScript.Workbook) {
  let i: number = 0;
  let increm: number = 2;

  let oldWorksheet: ExcelScript.Worksheet = workbook.getWorksheet("oldWorksheet");
  let newWorksheet: ExcelScript.Worksheet = workbook.getWorksheet("newWorksheet");
  let oldRange: ExcelScript.Range = oldWorksheet.getRange("A1").getSurroundingRegion();
  let oldRowCount: number = oldRange.getRowCount();
  let oldColCount: number = oldRange.getColumnCount();
  let oldRangeVals: (string | number | boolean)[][] = oldRange.getValues();
  let newRangeVals: (string | number | boolean)[][] = [];
  let columnCounter: number = oldColCount / increm;

  for (let i = 0; i < oldRowCount; i  ) {
    let tempVals: (string | number | boolean)[] = [];
    for (let j = 0; j < oldColCount; j =increm){
      tempVals.push(oldRangeVals[i][j]);
    }
    newRangeVals.push(tempVals);
  }
  let newRange: ExcelScript.Range = newWorksheet.getRange("A1").getResizedRange(oldRowCount - 1, columnCounter - 1);
  newRange.setValues(newRangeVals);
}

This code gets a count of the current rows and columns in the current region of the old sheet. It also gets the values of the cells in the current region of the old sheet. It iterates through the array using the requested parameters. The resulting values are then written to a new array. Once the loops are finished, the range in the new sheet is resized to the new array. And after that's done, the new array is written to the new sheet using setValues.