[Excel] Office Scripts

Create a table

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Add a table that has headers using the data from B2:E5.
    sheet.addTable("B2:E5", true);
}

Create a chart

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Create a column chart using the data from B3:C5.
    let chart = sheet.addChart(
        ExcelScript.ChartType.columnStacked,
        sheet.getRange("B3:C5")
    );

    // Set the margin of the chart to be 100 pixels from the top of the screen.
    chart.setTop(100);
}

Samples

Read and log one cell

This sample reads the value of A1 and prints it to the console.

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the value of cell A1.
  let range = selectedSheet.getRange("A1");
  
  // Print the value of A1.
  console.log(range.getValue());
}

Read the active cell

This script logs the value of the current active cell. If multiple cells are selected, the top-leftmost cell will be logged.

function main(workbook: ExcelScript.Workbook) {
  // Get the current active cell in the workbook.
  let cell = workbook.getActiveCell();

  // Log that cell's value.
  console.log(`The current cell's value is ${cell.getValue()}`);
}

...

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();
    let range = selectedSheet.getUsedRange(true);

	// If the used range is empty, end the script.
	if (!range) {
	console.log(`No data on this sheet.`);
		return;
	}

  	// Log the address of the used range. ex) Sheet1!B2:E5
  	console.log(`Used range for the worksheet: ${range.getAddress()}`);

    // 마지막 셀
  	console.log(range.getLastCell().getAddress())
  	
  	// 마지막 셀에 색상 적용
	range.getLastCell().getFormat().getFill().setColor("Cyan");

	// last colmun number
  	let lastColNumber = range.getColumnCount() - 1

  	// 7행 위로 삭제
  	selectedSheet.getRange(`A1:${range.getCell(7, lastColNumber).getAddress()}`).delete(ExcelScript.DeleteShiftDirection.up)
}