Incrementing Alphabet Counter for Excel Online in Office Script

Question

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!

Have you looked into using getRangeByIndexes()? That should allow you to specify the range using numerical values. So you should be able to increment as needed within a loop. — Brian Gonzalez, Sep 30 '21 at 15:56
@BrianGonzalez We're quite new to this particular language, so we haven't heard of getRangeByIndexes() until now, but we're looking into it now, thank you. Although there is little material available online about it, so we're not entirely sure how we would use this, would we adjust x & y to equal getRangeByIndexes(something) or do you mean for us to use this in another way? — Obahar, Sep 30 '21 at 16:54
Actually, I think you can combine getCell() with GetEntireColumn() to get what you want. I'll post the code as an answer. — Brian Gonzalez, Sep 30 '21 at 18:25

score 2 · Accepted Answer · edited Nov 05 '21 at 04:13

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.

Sorry for the delay, we posted this near the end of the work day expecting several hours before an answer. This worked perfectly! Thank you, we did have to make some slight adjustments to the loop as this experiment of ours was quite the jump from our automated code! Just a simple if else loop that would trigger for a specific instance, otherwise, this code was exactly what we were looking for. — Obahar, Oct 01 '21 at 12:28

Incrementing Alphabet Counter for Excel Online in Office Script

1 Answers1