TypeScript Code

Writing

Excel.run(function (context) { 
                context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").formulas = "=RAND()*17"; 
    return context.sync(); 
}).catch(function (error) { 
   console.log(error); 
}); 

Excel.run(function (context) { 
   var range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:B2"); 
   range.formulas = [["=RAND()*12", "=RAND()*19"], ["=A1*.7", "=B1*.9"]]; 
   return context.sync();             
}).catch(function (error) { 
   console.log(error); 
}); 

Reading

Excel.run(function (context) { 
    var range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").load("formulas"); 
     return context.sync().then(function() { 
      for (var i = 0; i < range.formulas.length; i++) { 
          for (var j = 0; j < range.formulas[i].length; j++) { 
              console.log(range.formulas[i][j]); 
          } 
      } 
   console.log("done");      
   }); 
}).catch(function (error) { 
   console.log(error); 
}); 

Read calculation mode

Excel.run(function (context) { 
   var application = context.workbook.application.load("calculationMode"); 
   return context.sync().then(function () { 
      console.log(application.calculationMode);                                           
   });           
}).catch(function (error) { 
   console.log(error); 
}); 

Calculate workbooks

Excel.run(function (context) { 
   context.workbook.application.calculate(Excel.CalculationType.full); 
   return context.sync();             
}).catch(function (error) { 
   console.log(error); 
}); 

This script helps you control calculation mode.

function main(workbook: ExcelScript.Workbook) { 
    // Set the calculation mode to manual. 
    workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual); 
    // Get and log the calculation mode. 
    const calcMode = workbook.getApplication().getCalculationMode();    
    console.log(calcMode); 
    // Manually calculate the file. 
    workbook.getApplication().calculate(ExcelScript.CalculationType.full); 
} 

This script sets cell "A1" to the number 2 and cell "B1" to a formula.
It then displays how Excel stores the cell's formula and value separately.

function main(workbook: ExcelScript.Workbook) { 
  let selectedSheet = workbook.getActiveWorksheet(); 
  let a1 = selectedSheet.getRange("A1"); 
  a1.setValue(2); 

  let b1 = selectedSheet.getRange("B1") 
  b1.setFormula("=(2*A1)"); 

  console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`); 
} 

This script transposes the range "A1:D2" to "A4:B7" by using the TRANSPOSE function. If the transpose results in a #SPILL error, it clears the target range and applies the formula again.

function main(workbook: ExcelScript.Workbook) { 
  let sheet = workbook.getActiveWorksheet(); 
  // Use the data in A1:D2 for the sample. 
  let dataAddress = "A1:D2" 
  let inputRange = sheet.getRange(dataAddress); 

  // Place the transposed data starting at A4. 
  let targetStartCell = sheet.getRange("A4"); 

  // Compute the target range. 
  let targetRange = targetStartCell.getResizedRange(inputRange.getColumnCount() - 1, inputRange.getRowCount() - 1); 

  // Call the transpose helper function. 
  targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`); 

  // Check if the range update resulted in a spill error. 
  let checkValue = targetStartCell.getValue() as string; 
  if (checkValue === '#SPILL!') {  
    // Clear the target range and call the transpose function again. 
    console.log("Target range has data that is preventing update. Clearing target range."); 
    targetRange.clear(); 
    targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`); 
  } 

  // Select the transposed range to highlight it. 
  targetRange.select(); 
}