kiến thức Office Scripts, một giải pháp tự động hóa khác bên cạnh VBA trong Excel

function main(workbook: ExcelScript.Workbook)
{
  let worksheets = workbook.getWorksheets();
  worksheets.forEach(worksheet => {
    let wsUsedRange = worksheet.getUsedRange();
    if (!wsUsedRange)
    {
      worksheet.delete();
    }
  });
}

function main(workbook: ExcelScript.Workbook)
{
  let selectedRange = workbook.getSelectedRange();
  let rowCount = selectedRange.getRowCount();
  let columnCount = selectedRange.getColumnCount();
  let rangeValues = selectedRange.getValues();
  for (let i = 0; i < rowCount; i++)
  {
    for (let j = 0; j < columnCount; j++)
    {
      let result = countIf(selectedRange, rangeValues[i][j])
      if (result > 1 )
      {
        let cell = selectedRange.getCell(i,j);
        cell.getFormat().getFill().setColor("green");
      }
    }
  }

  function countIf(range: ExcelScript.Range, criteria: ExcelScript.RangeValue): number
  {
    let duplicatesCount = 0;
    let rowCount = range.getRowCount();
    let columnCount = range.getColumnCount();
    let rangeData = range.getValues();
    for (let i = 0; i < rowCount; i++)
    {
      for (let j = 0; j < columnCount; j++)
      {
        let cellValue = rangeData[i][j];
        if (cellValue === criteria)
        {
          duplicatesCount = duplicatesCount + 1;
        }
      }
    }
    return duplicatesCount;
  }
}

async function main(workbook: ExcelScript.Workbook) {
  let worksheet = workbook.getActiveWorksheet();
  let sourceLang = worksheet.getRange("B2").getValue();
  let destinationLang = worksheet.getRange("C2").getValue();
  let textToTranslate = worksheet.getRange("B3").getValue();
  let fetchResult = await fetch("https://translate.googleapis.com/translate_a/single?client=gtx&sl=" + sourceLang + "&tl=" + destinationLang + "&dt=t&q=" + encodeURI(textToTranslate.toString()))
  if (fetchResult.ok)
  {
    let response = await fetchResult.text();
    worksheet.getRange("C3").setValue((JSON.parse(response)[0][0][0]));
  }
}

function main(workbook: ExcelScript.Workbook)
{
  let selectedRange = workbook.getSelectedRange();
  let selectedRangeValues = selectedRange.getValues();
  let rowCount: number;
  let columnCount: number;
  let row: number;
  let column: number;
  rowCount = selectedRange.getRowCount();
  columnCount = selectedRange.getColumnCount();
  for (row = 0; row < rowCount; row++)
  {
    for (column = 0; column < columnCount; column++)
    {
      let cell = selectedRange.getCell(row, column);
      if (cell.getFormula)
      {
        cell.setValue(selectedRangeValues[row][column]);
      }
    }
  }
}