Question 305375 in Javascript Question posted in
A very good W3school tutorial can be found here.

Excel Javascript API Detect Strikethrough Text & Sort

I’m trying to use the Excel Javascript APi to sort text based on Strikethrough. What I would like to do is detect all strikethrough text in the range & set to say Red then sort.This is what I have but getting error..

ebugInfo: Object
code: "PropertyNotLoaded"
message: "The property ‘strikethrough’ is not available. Before reading the property’s value, call the load method on the containing object and call "context.sync()" on the associated request context."

$("#run").click(() => tryCatch(run));
async function run() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    // Add values to the range
    const range = sheet.getRange("A5:M500");

    let dataRange = sheet.getRange("A5:M500").load(["rowCount", "format", "values","font","strikethrough"]);

    console.log(dataRange.toJSON());
    await context.sync();

    for (var row = 0; row < dataRange.rowCount; row++) {
      dataRange.getRow(row).format.fill.clear();

      if (dataRange.getCell(row, 0).format.font.strikethrough == true) {
        dataRange.getRow(row).format.font.color = "Red";
      }
    }   
    
    // Sort the range
    const sortFields = [
      {
        key: 1,
        sortOn: Excel.SortOn.fontColor,
        ascending: false
      },
       {
        key: 0,
        ascending: true
      }
    ];
    range.sort.apply(sortFields);

    await context.sync();
  });
}
    
    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    OfficeExtension.config.extendedErrorLogging = true;

$("#run").click(() => tryCatch(run));
async function run() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    //let myUsedRange = sheet.getUsedRange();
    //myUsedRange.load(["rowCount", "format/font/strikethrough"]);
    //console.log(JSON.stringify(myUsedRange.rowCount, null, 4));
    

    let dataRange = sheet.getRange("A5:M44").load(["rowCount", "rowIndex"]);
    await context.sync();

    //console.log(JSON.stringify(dataRange, null, 4));

    for (var row = 0; row < dataRange.rowCount; row++) {
      dataRange.getRow(row).format.fill.clear();

      let dataCell = dataRange.getCell(row, 1).load(["format/font/strikethrough"]);
      await context.sync();

      //console.log(JSON.stringify(dataCell, null, 4));

      if (dataCell.format.font.strikethrough != null) {
        if (dataCell.format.font.strikethrough == true) {
          dataRange.getRow(row).format.font.color = "Red";
        }
      }
    } 
    
    await context.sync();

    tryCatch(sort);

    await context.sync();

  });
}

async function sort() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    // Add values to the range
    let range = sheet.getRange("A5:M44");
    // Sort the range
    const sortFields = [
      {
        key: 1,
        sortOn: Excel.SortOn.fontColor,
        ascending: true
      },
      {
        key: 1,
        sortOn: Excel.SortOn.value,
        ascending: true
      }];
    range.sort.apply(sortFields);

    await context.sync();

  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

    enter code here

  2. 0

    You are not using the load() method correctly. Try replacing:

     let dataRange = sheet.getRange("A5:M500").load(["rowCount", "format", "values","font","strikethrough"]);

    with

     let dataRange = sheet.getRange("A5:M500").load(["rowCount", "format/font/strikethrough", "values"]);

    Also, see load() for how to use the method.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search