Scripting Examples

This script sets Sheet1!A1's value to "Hello, world!". It can be called like =CASTSCRIPT("Hello World", "HelloWorld")

using Sheetcast.ImmutableExcelModel.Values;
using Sheetcast.Kernel5.Mutations;
using Sheetcast.Scripting;

public class HelloWorld
{
    // the entry point, scripts must have a single Execute method
    public void Execute(ISheetcastScriptContext context)
    {
        // changes made to the workbook are done by applying mutations to it
        context.Apply(new[]
        {
            // this mutation describes a single action, to set a cell's value to a string
            new CellSetValueMutation("Sheet1", 1, 1, new StringEValue("Hello, world!"))
        });
    }
}

This script reads the value of every cell in the provided reference, and either increments it by the provided value if it's numeric or otherwise overwrites it by the provided value. It can be called like =CASTSCRIPT("Increment by 5", "SetValues", Sheet1!A1, 5)

using System.Collections.Generic;
using System.Linq;
using Sheetcast.ImmutableExcelModel.Values;
using Sheetcast.Kernel5.Mutations;
using Sheetcast.Scripting;

public class SetValues
{
    // the Execute method accepts function arguments as additional parameters
    // the script is not executed if there is a number or type mismatch
    public void Execute(ISheetcastScriptContext context, IEValue reference, DoubleEValue value)
    {
        switch (reference)
        {
            // the reference is for a single cell, like Sheet1!A1
            case ReferenceCellEValue cellReference:
                Handle(context, cellReference, value);
                break;
            // the reference is for a range of cells, like Sheet1!A1:C2
            case ReferenceRectangleEValue rectangleReference:
                Handle(context, rectangleReference, value);
                break;
            // a 3D reference, like Sheet1:Sheet2!A1:C2
            case ReferenceCuboidEValue cuboidReference:
                Handle(context, cuboidReference, value);
                break;
        }
    }

    private void Handle(ISheetcastScriptContext context, ReferenceCellEValue cellReference, DoubleEValue value)
    {
        // looks up a single worksheet
        var sheet = context.ESheet.Book.GetSheet(cellReference.SheetName);

        // reads a cell's executed value
        var cellValue = sheet.GetValue(cellReference.RowNumber, cellReference.ColumnNumber);

        context.Apply(new[]
        {
            new CellSetValueMutation(cellReference.SheetName, cellReference.RowNumber, cellReference.ColumnNumber, IncrementOrOverwrite(cellValue, value))
        });
    }

    private void Handle(ISheetcastScriptContext context, ReferenceRectangleEValue rectangleReference, DoubleEValue value)
    {
        var sheet = context.ESheet.Book.GetSheet(rectangleReference.SheetName);
        var rowMutations = new List<CellSetValueBulkByRowMutation.Row>();

        for (var rowNumber = rectangleReference.TopRowNumber; rowNumber <= rectangleReference.BottomRowNumber; rowNumber++)
        {
            var columnAndValueMutations = new List<CellSetValueBulkByRowMutation.ColumnAndValue>();

            for (var columnNumber = rectangleReference.LeftColumnNumber; columnNumber <= rectangleReference.RightColumnNumber; columnNumber++)
            {
                var cellValue = sheet.GetValue(rowNumber, columnNumber);
                columnAndValueMutations.Add(new CellSetValueBulkByRowMutation.ColumnAndValue(columnNumber, IncrementOrOverwrite(cellValue, value)));
            }

            rowMutations.Add(new CellSetValueBulkByRowMutation.Row(rowNumber, columnAndValueMutations));
        }

        context.Apply(new[]
        {
            // it is more efficient to group up similar mutations together, rather than applying them individually
            new CellSetValueBulkByRowMutation(new[]
            {
                new CellSetValueBulkByRowMutation.Sheet(sheet.Name, rowMutations)
            })
        });
    }

    private void Handle(ISheetcastScriptContext context, ReferenceCuboidEValue cuboidReference, DoubleEValue value)
    {
        var sheetMutations = new List<CellSetValueBulkByColumnMutation.Sheet>();

        foreach (var sheet in context.ESheet.Book.Sheets.SkipWhile(sheet => sheet.Name != cuboidReference.StartSheetName))
        {
            var columnMutations = new List<CellSetValueBulkByColumnMutation.Column>();

            for (var columnNumber = cuboidReference.LeftColumnNumber; columnNumber <= cuboidReference.RightColumnNumber; columnNumber++)
            {
                var rowAndValueMutations = new List<CellSetValueBulkByColumnMutation.RowAndValue>();

                for (var rowNumber = cuboidReference.TopRowNumber; rowNumber <= cuboidReference.BottomRowNumber; rowNumber++)
                {
                    var cellValue = sheet.GetValue(rowNumber, columnNumber);
                    rowAndValueMutations.Add(new CellSetValueBulkByColumnMutation.RowAndValue(rowNumber, IncrementOrOverwrite(cellValue, value)));
                }

                columnMutations.Add(new CellSetValueBulkByColumnMutation.Column(columnNumber, rowAndValueMutations));
            }

            sheetMutations.Add(new CellSetValueBulkByColumnMutation.Sheet(sheet.Name, columnMutations));

            if (sheet.Name == cuboidReference.EndSheetName)
            {
                break;
            }
        }

        context.Apply(new[]
        {
            new CellSetValueBulkByColumnMutation(sheetMutations)
        });
    }

    private IWritableRawValue IncrementOrOverwrite(IEValue cellValue, DoubleEValue value) => cellValue is DoubleEValue numericCellValue ? new DoubleEValue(numericCellValue.Value + value.Value) : value;
}

This script retrieves stock information from an external API and inserts it into a new row. It assumes that the provided name refers to the last row of existing data, which is formatted. It can be called like =CASTSCRIPT("Get IBM Prices", "GetStockPrices", "LastStockPriceRow", "IBM")

using System;
using System.Linq;
using System.Net.Http;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading.Tasks;
using Sheetcast.ImmutableExcelModel.Values;
using Sheetcast.ImmutableExcelModel.Values.Conversion;
using Sheetcast.Kernel5.Mutations;
using Sheetcast.Scripting;

public class GetStockPrices
{
    private const string APIKey = "";

    public void Execute(ISheetcastScriptContext context, StringEValue nameString, StringEValue symbol)
    {
        using var httpClient = new HttpClient
        {
            BaseAddress = new Uri("https://www.alphavantage.co")
        };

        var responseTask = Task.Run(() => httpClient.GetStringAsync($"query?function=GLOBAL_QUOTE&symbol={symbol.Value}&apikey={APIKey}"));
        responseTask.Wait();

        var response = JsonSerializer.Deserialize<Response>(responseTask.Result, new JsonSerializerOptions
        {
            NumberHandling = JsonNumberHandling.AllowReadingFromString
        });

        // looks up a single workbook scoped name
        var name = context.ESheet.Book.BookDefinedNames.Single(name => name.Name == nameString.Value);

        // parses the definition string into its component elements
        var nameReference = ReferenceConverter.ParseToBlockDefintionA1(name.Definition);

        // describes the two ranges we're interested in, a source to pull information from and a target to copy that information to
        var source = CellSpecifier.Range(nameReference.StartSheetName, nameReference.TopRowNumber, nameReference.LeftColumnNumber, nameReference.TopRowNumber, nameReference.RightColumnNumber);
        var target = CellSpecifier.Range(nameReference.StartSheetName, nameReference.TopRowNumber + 1, nameReference.LeftColumnNumber, nameReference.TopRowNumber + 1, nameReference.RightColumnNumber);

        // the new definition string, after having been adjusted for the insert
        var newNameDefinition = (nameReference with { TopRowNumber = nameReference.TopRowNumber + 1, BottomRowNumber = nameReference.BottomRowNumber + 1 }).GetBlockDefinition();

        var index = nameReference.LeftColumnNumber;

        context.Apply(new Mutation[]
        {
            // inserts a new row
            new RowInsertMutation(nameReference.StartSheetName, nameReference.TopRowNumber + 1),

            // copies the cell formatting down
            new CellCopyCellXfIdMutation(source, target),
            new ConditionalFormattingCopyMutation(source, target),

            // updates the name's definition
            new DefinedNameSetDefinitionMutation(null, name.Name, newNameDefinition),

            new CellSetValueBulkByRowMutation(new[]
            {
                new CellSetValueBulkByRowMutation.Sheet(nameReference.StartSheetName, new[]
                {
                    new CellSetValueBulkByRowMutation.Row(nameReference.TopRowNumber + 1, new[]
                    {
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new StringEValue(response.GlobalQuote.Symbol)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.Open)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.High)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.Low)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.Price)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.Volume)),
                        // dates and times are represented by serial numbers
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.LatestTradingDay.ToOADate())),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.PreviousClose)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(response.GlobalQuote.Change)),
                        new CellSetValueBulkByRowMutation.ColumnAndValue(index++, new DoubleEValue(double.Parse(response.GlobalQuote.ChangePercent.TrimEnd('%')) / 100)),
                    })
                })
            })
        });
    }

    private class Response
    {
        [JsonPropertyName("Global Quote")]
        public GlobalQuote GlobalQuote { get; set; }
    }

    private class GlobalQuote
    {
        [JsonPropertyName("01. symbol")]
        public string Symbol { get; set; }

        [JsonPropertyName("02. open")]
        public double Open { get; set; }

        [JsonPropertyName("03. high")]
        public double High { get; set; }

        [JsonPropertyName("04. low")]
        public double Low { get; set; }

        [JsonPropertyName("05. price")]
        public double Price { get; set; }

        [JsonPropertyName("06. volume")]
        public double Volume { get; set; }

        [JsonPropertyName("07. latest trading day")]
        public DateTime LatestTradingDay { get; set; }

        [JsonPropertyName("08. previous close")]
        public double PreviousClose { get; set; }

        [JsonPropertyName("09. change")]
        public double Change { get; set; }

        [JsonPropertyName("10. change percent")]
        public string ChangePercent { get; set; }
    }
}