How to copy a JavaScript 2D array to clipboard to paste it in excel?

Example 2D array:

var arr = [
  ["Mike", "Cane", 23],
  ["Jeff", "Meyers", 46],
  ["Thomas", "Bush", 67]
]

How do I copy a 2D array to the clipboard to then paste it in an excel spreadsheet? The rows and columns should be preserved as it is in the array. It should work the same way as if I would mark a range of cells in excel and then paste it back in.

Answer

This solution works amazing. It uses (CSV like) line breaks for a new row and tab for a new cell. Pasting text like this in an Excel, OpenOffice / LibreOffice Calc spreadsheet, it will detect it as multiple cells. It also works with Google Docs.

function copy2DToClipboard(array) {
  var csv = '', row, cell;
  for (row = 0; row < array.length; row++) {
    for (cell = 0; cell < array[row].length; cell++) {
      csv += (array[row][cell]+'').replace(/[nt]+/g, ' ');
      if (cell+1 < array[row].length) csv += 't';
    }
    if (row+1 < array.length) csv += 'n';
  }
  copyTextToClipboard(csv);
}

// copied from https://stackoverflow.com/questions/400212/how-do-i-copy-to-the-clipboard-in-javascript
function fallbackCopyTextToClipboard(text) {
  var textArea = document.createElement("textarea");
  textArea.value = text;

  // Avoid scrolling to bottom
  textArea.style.top = "0";
  textArea.style.left = "0";
  textArea.style.position = "fixed";

  document.body.appendChild(textArea);
  textArea.focus();
  textArea.select();

  try {
    var successful = document.execCommand('copy');
    var msg = successful ? 'successful' : 'unsuccessful';
    // console.log('Fallback: Copying text command was ' + msg);
  } catch (err) {
    console.error('Fallback: Oops, unable to copy', err);
  }

  document.body.removeChild(textArea);
}
function copyTextToClipboard(text) {
  if (!navigator.clipboard) {
    fallbackCopyTextToClipboard(text);
    return;
  }
  navigator.clipboard.writeText(text).then(function() {
    // console.log('Async: Copying to clipboard was successful!');
  }, function(err) {
    console.error('Async: Could not copy text: ', err);
  });
}

Test:

function onTest() {
  const arr = [
    ["Mike", "Cane", 23],
    ["Jeff", "Meyers", 46],
    ["Thomas", "Bush", 67]
  ];
  copy2DToClipboard(arr);
  document.getElementById('test').innerText = 'Copied!';
}

function copy2DToClipboard(array) {
  var csv = '', row, cell;
  for (row = 0; row < array.length; row++) {
    for (cell = 0; cell < array[row].length; cell++) {
      csv += (array[row][cell]+'').replace(/[nt]+/g, ' ');
      if (cell+1 < array[row].length) csv += 't';
    }
    if (row+1 < array.length) csv += 'n';
  }
  copyTextToClipboard(csv);
}

// copied from https://stackoverflow.com/questions/400212/how-do-i-copy-to-the-clipboard-in-javascript
function fallbackCopyTextToClipboard(text) {
  var textArea = document.createElement("textarea");
  textArea.value = text;
  
  // Avoid scrolling to bottom
  textArea.style.top = "0";
  textArea.style.left = "0";
  textArea.style.position = "fixed";

  document.body.appendChild(textArea);
  textArea.focus();
  textArea.select();

  try {
    var successful = document.execCommand('copy');
    var msg = successful ? 'successful' : 'unsuccessful';
    // console.log('Fallback: Copying text command was ' + msg);
  } catch (err) {
    console.error('Fallback: Oops, unable to copy', err);
  }

  document.body.removeChild(textArea);
}
function copyTextToClipboard(text) {
  if (!navigator.clipboard) {
    fallbackCopyTextToClipboard(text);
    return;
  }
  navigator.clipboard.writeText(text).then(function() {
    // console.log('Async: Copying to clipboard was successful!');
  }, function(err) {
    console.error('Async: Could not copy text: ', err);
  });
}

<button onclick="onTest()" id="test">Copy to clipboard.</button>

Edit:

This solution does not support cells with internal line breaks or tabs. Non-ascii unicodes can cause problems with some spreadsheet programs.

See also How do I copy to the clipboard in JavaScript?