Excel.run, pass and return context as a variable, where does context come from?

user_8282697 0
Viewed 140

I have a big Google Apps Script project working on Google Spreadsheets and I am trying to convert it to an office-js add-in that works on Excel Workbooks. I understand that it is good practice to put everything that will be calling excel specific functions (directly interacting with the workbook) into an Excel.run() function so it does a proper clean-up and no memory leaks occur. I also understand I should do context.sync() as little as possible to optimize performance.

Here are my questions, (I think some of them come from my incomplete grasp of how js works; these things GAS handled without me needing to question them) :

1a) When we put our block of code in

Excel.run(context => {
 //code that does stuff with context; 
context.sync();
});

where does the context come from? Is this equivalent to

Excel.run(()=> {
let context = new Excel.RequestContext; 
//code that does stuff with context; 
context.sync();
});

1b) Also, if context gets generated with every new function why would I ever return context.sync() and not just context.sync()?

  1. Is a new, second context generated in this case and what happens to it?
function handle_error(e){//second context generated in case of error
let context=New Excel.RequestContext;
context.workbook.load('name');
await context.sync();
some_logging_function(context.workbook.name, e);
}
function data_func(some_data: Non-Excel-Interface): Other-Non-Excel-Interface{
   //manipulate data
   //in case of error
   handle_error(e);
   //continue with data massaging
   return altered_data;
}

Excel.run(context=>{ //first context
context.workbook.worksheets.getItem('Sheet1').getUsedRange().load('values');
context.sync();
let values = context.workbook.worksheets.getItem('Sheet1').getUsedRange().values;
let some_data: Non-Excel-Interface = {sheetName: 'Sheet1', data: values};
let new_vals = data_func(some_data);
context.workbook.worksheets.getItem('Sheet1').getUsedRange().values = new_vals.new_data;
context.sync();
});
  1. If I put my main code inside Excel.run, then pass and return context: Excel.RequestContext and range: Excel.Range in other functions do I need Excel.run() in those functions, too? In other words, should the code inside functions a() and b() be inside Excel.run()?
function a(rng: Excel.Range, values:string[][]):Excel.Range{
  rng.values = values;
  return rng;
}

function b(context: Excel.RequestContext): Excel.RequestContext{
  context.workbook.load('name');//load name property, but don't context.sync()
  return context;
}

Excel.run(async context=>{
  context = b(context);
  let rng =  context.workbook.worksheets.getItem('Sheet1').getUsedRange();
  rng.load('values');
  await context.sync();//values property and workbook name property must be available now
  rng = a(rng, [['aa', 'bb', 'cc']]);
  await context.sync();//new values must be available now
  console.log(context.workbook.name, rng.values);//should show the title of the workbook and the newly assigned values of the range
});
  1. Also, what is the advantage of asynchronous functions if I have to explicitly wait every time I need a value? I mean, if I am going to use context.sync() sparingly, that means I use it only when I desperately need it, so it must always come with await. So why not make context.sync() synchronous by default?
exceljs office-js excel javascript
2 Answers

I'll try to answer some of these questions and try to get some help for the others. I also recommend the book Building Office Add-ins for an understanding of the Office JavaScript library. See this too, if you haven't already: Application specific API model.

1a. Yes. That's essentially correct. Under the hood, Excel.run creates an Office.RequestContext object and passes it to the batch function parameter. (But your two code blocks are not literally equivalent. You would not call Excel.run AND explicitly create a RequestContext object.)

1b. From skimming the book I linked to, I think that you have to return what the book calls the meta-promise so that the Excel.run can resolve the Promise that it returns. Here's an example from the book:

Excel.run(function (context) {
  var selectionRange = context.workbook.getSelectedRange();
  selectionRange.format.fill.clear();

  selectionRange.load("values");
  
  return context.sync()
    .then(function () {
      var rowCount = selectionRange.values.length;
      var columnCount = selectionRange.values[0].length;
      for (var row = 0; row < rowCount; row++) {
        for (var column = 0; column < columnCount; column ++) {
          if (selectionRange.values[row][column] > 50) {
            selectionRange.getCell(row, column)
             .format.fill.color = "yellow";
          }
        }
      }
    })
    .then(context.sync);
  
}).catch(OfficeHelpers.Utilities.log);

  1. From skimming the book I linked to, I think that the answer is yes; the Excel.run always creates a new context object and passes it to the batch function. There are techniques and an override of Excel.run that enable you to pass an object created in one context to another call of Excel.run, but these are intended for use with independent calls of Excel.run, not nested calls, as in your case.

  2. No. You should not call Excel.run inside a or b.

  3. I think there are scenarios in which you would not need to await context.sync. For example, when all the code in the parent function that comes after the context.sync only affects the UI of a task pane and does not depend on reading any data from the current Office document. The good practice of minimizing calls of context.sync is because it requires a round-trip between the document and JavaScript runtime in which the add-in code is running (on the user's computer). This would be true regardless of whether context.sync is synchronous or not.

user_61900310

For 1a, here's one of the descriptions of how the Run function works which I'm getting from ScriptLab's intellisense:

A function that takes in a RequestContext and returns a promise (typically, just the result of "context.sync()"). The context parameter facilitates requests to the Excel application. Since the Office add-in and the Excel application run in two different processes, the RequestContext is required to get access to the Excel object model from the add-in.

In terms of 1b, I don't think so. RequestContext does not seem to be an object you can instantiate yourself.

EDIT: actually it does look like this is possible. Please see below:

$("#run").click(() => tryCatch(run));

async function run() {
  await Excel.run(async () => {
    let ctx:Excel.RequestContext = new Excel.RequestContext();
    let wb: Excel.Workbook = ctx.workbook
    let rang: Excel.Range = wb.getSelectedRange()
    rang.load("address")
    await ctx.sync()
    console.log(rang.address)
  });
}

/** 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);
  }
}

I'd recommend against using this approach. Passing in anonymous functions, like in the original example, is very common in JavaScript. So this would likely be considered a bad practice.

user_168648690
Related