UUoocl/Update OBS Text Sources with Excel.EXCEL.yaml

## Update OBS Text Sources with Excel.EXCEL.yaml
name: Update OBS Text Sources with Excel
description: >-
  This script gets all the text sources from OBS. When a text value is updated
  in Excel, the update also appears in OBS.
host: EXCEL
api_set: {}
script:
  content: |
    $("#register-event-handlers").click(() => tryCatch(registerEventHandlers));

    var obs = new OBSWebSocket();

    async function registerEventHandlers() {
      await Excel.run(async (context) => {
        //connect to OBS Websocket localhost
        //Get websocket connection info
        //Enter the websocketIP address
        const websocketIP = document.getElementById("IP").value;
        //Enter the OBS websocket port number
        const websocketPort = document.getElementById("Port").value;
        //Enter the OBS websocket server password
        const websocketPassword = document.getElementById("PW").value;

        console.log(`ws://${websocketIP}:${websocketPort}`);
        // connect to OBS websocket
        try {
          const { obsWebSocketVersion, negotiatedRpcVersion } = await obs.connect(
            `ws://${websocketIP}:${websocketPort}`,
            websocketPassword,
            {
              rpcVersion: 1
            }
          );
          console.log(`Connected to server ${obsWebSocketVersion} (using RPC ${negotiatedRpcVersion})`);
        } catch (error) {
          console.error("Failed to connect", error.code, error.message);
        }
        obs.on("error", (err) => {
          console.error("Socket error:", err);
        });

        //Get all the Text Sources from OBS
        const textSource = await obs.call("GetInputList", { inputKind: "text_gdiplus_v2" });

        // Add a 'on changed' event handler for the workbook.
        let tables = context.workbook.tables;
        tables.onChanged.add(onChange);
        console.log("A handler has been registered for the table collection onChanged event");
        await context.sync();
        setup();
      });
    }

    async function setup() {
      await Excel.run(async (context) => {
        //Delete the 'Sample' sheet
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        //Add a sheet named 'Sample
        const sheet = context.workbook.worksheets.add("Sample");

        //Create a Table to store the Text sources
        createSourceTable(sheet);
        //sync changes
        await context.sync();
        //Get the current content for each Text source
        getSourceSettings();

        let format = sheet.getRange().format;
        format.autofitColumns();
        format.autofitRows();

        sheet.activate();
        await context.sync();
      });
    }

    async function createSourceTable(sheet: Excel.Worksheet) {
      await Excel.run(async (context) => {
        //get the 'Sample' worksheet
        let sheet = context.workbook.worksheets.getItem("Sample");

        //create a table names SourceTable with 2 columns
        let sourceTable = sheet.tables.add("A7:B7", true);
        sourceTable.name = "SourceTable";
        sourceTable.getHeaderRowRange().values = [["inputName", "Setting"]];

        //Get a list of Text sources from OBS
        let textSources = await obs.call("GetInputList", { inputKind: "text_gdiplus_v2" });

        //transform the list of Text Sources to fit in the table
        textSources = Object.values(textSources).flat(1);
        let newData = textSources.map((item) => [item.inputName, item.inputKind]);

        //add the Text sources to the table
        sourceTable.rows.add(null, newData);

        //adjust table column widths
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();

        sheet.activate();
        await context.sync();
      });
    }

    async function getSourceSettings() {
      await Excel.run(async (context) => {
        //get the 'Sample' worksheet amd Source Table
        const sheet = context.workbook.worksheets.getItem("Sample");
        const sourceTable = sheet.tables.getItem("SourceTable");

        //get values from the Source Table
        const bodyRange = sourceTable.getDataBodyRange().load("values");
        const tableAddress = sourceTable.getDataBodyRange().load("address");

        //to read the table values, use the sync() method
        await context.sync();

        //read the Source table values
        const bodyValues = bodyRange.values;
        sheet.getRange(tableAddress.address).values = bodyValues;
        let sourceSetting;
        //for each Text Source get the current content
        for (let i = 0; i < bodyValues.length; i++) {
          //get settings from OBS
          sourceSetting = await obs.call("GetInputSettings", { inputName: bodyValues[i][0] });
          //transform the settings to fit in the table
          sourceSetting = Object.values(sourceSetting).flat(1);
          console.log(sourceSetting);
          let newData = sourceSetting.map((item) => [item.text]);
          console.log(newData);
          bodyValues[i][1] = newData[1][0];
        }
      });
    }

    async function onChange(event) {
      await Excel.run(async (context) => {
        //Get changed cell value and send it to OBS
        let table = context.workbook.tables.getItem(event.tableId);
        let worksheet = context.workbook.worksheets.getItem(event.worksheetId);
        let tablename = table.load("name");

        await context.sync();

        console.log(     "Handler for table collection onChanged event has been triggered. Data changed address: " + event.address
        );

        const newValue = worksheet.getRange(event.address);
        const inputName = newValue.getOffsetRange(0, -1);
        newValue.load("text");
        inputName.load("text");

        await context.sync();

        let textValue = newValue.text.toString();
        let inputValue = inputName.text.toString();

        console.log("Table Id : " + event.tableId);
        console.log("Table Name : " + table.name);

        //set OBS source text
        await obs.call("SetInputSettings", {
          inputName: inputValue,
          inputSettings: {
            text: textValue
          }
        });
      });
    }

    /** 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);
      }
    }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to use the Excel API <code>onChange</code> event and the OBS WebSocket js API to display an\n\t\tExcel cell's value in OBS.</p>\n\n\t\t<p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>OBS WebSocket Server Settings</h3>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server IP Address</span>\n\t<Input id=\"IP\" class=\"ms-Button\" value=\"localhost\"></Input>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server Port</span><br>\n\t<Input id=\"Port\" class=\"ms-Button\" value=\"4455\"></Input>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server Password</span><br>\n\t<Input type=\"password\" id=\"PW\" class=\"ms-Button\"></Input>\n\t<h3>OBS WebSocket Server Settings</h3>\n\n\t<button id=\"register-event-handlers\" class=\"ms-Button\">\n\t      <span class=\"ms-Button-label\">Connect to OBS WebSocket Server</span>\n    </button>\n\t<p>The <code>onChange</code> events being listened for are:</p>\n\t<ul>\n\t\t<li><code>Table</code></li>\n\t</ul>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<p>The console will log the values reported by the different <code>onSelectionChanged</code> events. Change the\n\t\tcell in the worksheet to see the results.</p>\n</section>"
  language: html
style:
  content: |
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |-
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1

  obs-websocket-js
	name: Update OBS Text Sources with Excel
	description: >-
	This script gets all the text sources from OBS. When a text value is updated
	in Excel, the update also appears in OBS.
	host: EXCEL
	api_set: {}
	script:
	content: \|
	$("#register-event-handlers").click(() => tryCatch(registerEventHandlers));

	var obs = new OBSWebSocket();

	async function registerEventHandlers() {
	await Excel.run(async (context) => {
	//connect to OBS Websocket localhost
	//Get websocket connection info
	//Enter the websocketIP address
	const websocketIP = document.getElementById("IP").value;
	//Enter the OBS websocket port number
	const websocketPort = document.getElementById("Port").value;
	//Enter the OBS websocket server password
	const websocketPassword = document.getElementById("PW").value;

	console.log(`ws://${websocketIP}:${websocketPort}`);
	// connect to OBS websocket
	try {
	const { obsWebSocketVersion, negotiatedRpcVersion } = await obs.connect(
	`ws://${websocketIP}:${websocketPort}`,
	websocketPassword,
	{
	rpcVersion: 1
	}
	);
	console.log(`Connected to server ${obsWebSocketVersion} (using RPC ${negotiatedRpcVersion})`);
	} catch (error) {
	console.error("Failed to connect", error.code, error.message);
	}
	obs.on("error", (err) => {
	console.error("Socket error:", err);
	});

	//Get all the Text Sources from OBS
	const textSource = await obs.call("GetInputList", { inputKind: "text_gdiplus_v2" });

	// Add a 'on changed' event handler for the workbook.
	let tables = context.workbook.tables;
	tables.onChanged.add(onChange);
	console.log("A handler has been registered for the table collection onChanged event");
	await context.sync();
	setup();
	});
	}

	async function setup() {
	await Excel.run(async (context) => {
	//Delete the 'Sample' sheet
	context.workbook.worksheets.getItemOrNullObject("Sample").delete();
	//Add a sheet named 'Sample
	const sheet = context.workbook.worksheets.add("Sample");

	//Create a Table to store the Text sources
	createSourceTable(sheet);
	//sync changes
	await context.sync();
	//Get the current content for each Text source
	getSourceSettings();

	let format = sheet.getRange().format;
	format.autofitColumns();
	format.autofitRows();

	sheet.activate();
	await context.sync();
	});
	}

	async function createSourceTable(sheet: Excel.Worksheet) {
	await Excel.run(async (context) => {
	//get the 'Sample' worksheet
	let sheet = context.workbook.worksheets.getItem("Sample");

	//create a table names SourceTable with 2 columns
	let sourceTable = sheet.tables.add("A7:B7", true);
	sourceTable.name = "SourceTable";
	sourceTable.getHeaderRowRange().values = [["inputName", "Setting"]];

	//Get a list of Text sources from OBS
	let textSources = await obs.call("GetInputList", { inputKind: "text_gdiplus_v2" });

	//transform the list of Text Sources to fit in the table
	textSources = Object.values(textSources).flat(1);
	let newData = textSources.map((item) => [item.inputName, item.inputKind]);

	//add the Text sources to the table
	sourceTable.rows.add(null, newData);

	//adjust table column widths
	sheet.getUsedRange().format.autofitColumns();
	sheet.getUsedRange().format.autofitRows();

	sheet.activate();
	await context.sync();
	});
	}

	async function getSourceSettings() {
	await Excel.run(async (context) => {
	//get the 'Sample' worksheet amd Source Table
	const sheet = context.workbook.worksheets.getItem("Sample");
	const sourceTable = sheet.tables.getItem("SourceTable");

	//get values from the Source Table
	const bodyRange = sourceTable.getDataBodyRange().load("values");
	const tableAddress = sourceTable.getDataBodyRange().load("address");

	//to read the table values, use the sync() method
	await context.sync();

	//read the Source table values
	const bodyValues = bodyRange.values;
	sheet.getRange(tableAddress.address).values = bodyValues;
	let sourceSetting;
	//for each Text Source get the current content
	for (let i = 0; i < bodyValues.length; i++) {
	//get settings from OBS
	sourceSetting = await obs.call("GetInputSettings", { inputName: bodyValues[i][0] });
	//transform the settings to fit in the table
	sourceSetting = Object.values(sourceSetting).flat(1);
	console.log(sourceSetting);
	let newData = sourceSetting.map((item) => [item.text]);
	console.log(newData);
	bodyValues[i][1] = newData[1][0];
	}
	});
	}

	async function onChange(event) {
	await Excel.run(async (context) => {
	//Get changed cell value and send it to OBS
	let table = context.workbook.tables.getItem(event.tableId);
	let worksheet = context.workbook.worksheets.getItem(event.worksheetId);
	let tablename = table.load("name");

	await context.sync();

	console.log( "Handler for table collection onChanged event has been triggered. Data changed address: " + event.address
	);

	const newValue = worksheet.getRange(event.address);
	const inputName = newValue.getOffsetRange(0, -1);
	newValue.load("text");
	inputName.load("text");

	await context.sync();

	let textValue = newValue.text.toString();
	let inputValue = inputName.text.toString();

	console.log("Table Id : " + event.tableId);
	console.log("Table Name : " + table.name);

	//set OBS source text
	await obs.call("SetInputSettings", {
	inputName: inputValue,
	inputSettings: {
	text: textValue
	}
	});
	});
	}

	/** 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);
	}
	}
	language: typescript
	template:
	content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to use the Excel API <code>onChange</code> event and the OBS WebSocket js API to display an\n\t\tExcel cell's value in OBS.</p>\n\n\t\t<p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>OBS WebSocket Server Settings</h3>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server IP Address</span>\n\t<Input id=\"IP\" class=\"ms-Button\" value=\"localhost\"></Input>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server Port</span><br>\n\t<Input id=\"Port\" class=\"ms-Button\" value=\"4455\"></Input>\n\t<span class=\"ms-Button-label\">Enter WebSocket Server Password</span><br>\n\t<Input type=\"password\" id=\"PW\" class=\"ms-Button\"></Input>\n\t<h3>OBS WebSocket Server Settings</h3>\n\n\t<button id=\"register-event-handlers\" class=\"ms-Button\">\n\t <span class=\"ms-Button-label\">Connect to OBS WebSocket Server</span>\n </button>\n\t<p>The <code>onChange</code> events being listened for are:</p>\n\t<ul>\n\t\t<li><code>Table</code></li>\n\t</ul>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<p>The console will log the values reported by the different <code>onSelectionChanged</code> events. Change the\n\t\tcell in the worksheet to see the results.</p>\n</section>"
	language: html
	style:
	content: \|
	section.samples {
	margin-top: 20px;
	}

	section.samples .ms-Button, section.setup .ms-Button {
	display: block;
	margin-bottom: 5px;
	margin-left: 20px;
	min-width: 80px;
	}
	language: css
	libraries: \|-
	https://appsforoffice.microsoft.com/lib/1/hosted/office.js
	@types/office-js

	office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
	office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

	core-js@2.4.1/client/core.min.js
	@types/core-js

	jquery@3.1.1
	@types/jquery@3.3.1

	obs-websocket-js