javascriptexcelgoogle-sheetsmarkdownpaste

How do I detect if a paste event came from Excel?


I'm wanting to implement a markdown editor that functions similar to Stack Exchange and Github, where users can paste table data from Google Sheets, Excel, etc, and it will convert the table to a GFM table.

Related: I've asked for a library that does this on Software Recommendations here.

I've found some open source code that does this conversion pretty well here.

Unfortunately, they haven't implemented their 'looksLikeTable' function.

A paste event that comes from Google Sheets will handily include the type "application/x-vnd.google-docs-embedded-grid_range_clip+wrapped" in pasteEvent.clipboardData.types.

However paste events that come from Excel (and Apple Numbers) just include an types array of:

[
    "text/plain",
    "text/html",
    "text/rtf",
    "Files"
]

Which is a problem, because a paste event from MS Word also contains the same array, so I need some way to distinguish paste events from of ordinary text from MS Word, from table data.

I guess technically I could write a solution where "if every row of the plain text has the same name of words, then treat it like a table", but is there something more robust I can do?


Solution

  • In an appeal to Cunningham's law, here's my current solution:

    function looksLikeTable(data: DataTransfer | null): boolean {
      if (!data) {
        return false;
      }
    
      // Google sheets
      if (data.types.find((v) => {
        return v.startsWith("application/x-vnd.google-docs-embedded-grid_range_clip");
      })) {
        return true;
      };
    
      const htmlData = data.getData("text/html");
      if (htmlData) {
        // MS Excel
        if (htmlData.includes("<meta name=ProgId content=Excel.Sheet>")) {
          return true;
        }
    
        // Numbers
        if (htmlData.startsWith("<table>") && htmlData.endsWith("</table>")) {
          return true;
        }
      }
    
      return false;
    }
    

    We use different techniques to determine the source. Google Sheets will include a type, MS Excel will have information in the HTML string. Numbers is a just a plain table.

    Presumably other data sources (eg. copied HTML tables, copied tables from within an MS Word document would need their own handlers). This is why I asked for a library.

    Perhaps a better approach over this, is instead of relying on the plain text rendition of the table, use the HTML version and use an HTML Table to Markdown tool to do the conversion.