Automating the new Google Sites via the Clipboard

Question

Many of Google's products such as Documents and Spreadsheets, allow automation via Google Apps Scripting. Noticeably missing is the new Google Sites.

I was playing around with a Google Site the other day and noticed that you can copy and paste large amounts of data, and it'll format it correctly, even between Google Sites! It'll format it to match the destination site while still retaining all the images, styles, elements, etc.

So while we don't have scripting for Google Sites yet, this got me thinking, is there a way to partially automate Google Site updating by copying significant bits of data to the user's clipboard? The user would then be able to go to the Google Site page and paste the contents of the clipboard, and it'll be formatted perfectly.

I tried using various clipboard managers to get a better understanding as to what Google Sites is putting in the clipboard, but was not able to get too much information. For example, Pastebot showed the contents as "Txt" when it clearly had much more rich metadata involved.

Ideally looking for a script that can be run from Google Apps Script in a Spreadsheet that puts this information in the user's clipboard.

Answer 1

First, the best way to examine the clipboard is to create a simple macOS application:

let pasteboard = NSPasteboard.general
for element in pasteboard.pasteboardItems! {
  for type in element.types {
    print(type)
    if let string = element.string(forType: type)?.prefix(30) {
      print(string)
    } else {
      let data = element.data(forType: type)
      print(data)
    }
  }
}

After running this script, you'll notice that when you copy data from Google Sites, it places content in 3 formats:

• Text
• public.html
• com.apple.WebKit.custom-pasteboard-data

Overwriting the contents of the clipboard with a subset of these shows that although public.html does paste something into Google Sites, the formatting is all off. Thus com.apple.WebKit.custom-pasteboard-data is the one we actually care about.

You can't convert the contents directly to a string as it'll crash the app or show gibberish depending on which encoding you select. If we write its data contents into a file on disk, we can examine its contents via Hex Fiend. There you'll see why that is the case:

It appears that the text starts off as simple Ascii encoding and is eventually JSON encoded as UTF-16. Interestingly enough, the format seems to be application/x-vnd.google-sites-document yet a Google search for this format doesn't yield any relevant results.

The next step is to go back to Google Sites, copy something with the word "Test" to the clipboard, and then use Hex Fiend to search for est (00 65 00 73 00 74) then try replacing the e with E, then copy that to the clipboard:

let url = URL(fileURLWithPath: "readdata")
let data = try! Data(contentsOf: url)
let pasteboardType = NSPasteboard.PasteboardType(rawValue: "com.apple.WebKit.custom-pasteboard-data")
let pasteboard = NSPasteboard.general
pasteboard.clearContents()
assert(pasteboard.setData(data, forType: pasteboardType))
assert(pasteboard.pasteboardItems![0].types.count == 1)

After running that code and pasting on Google Sites, it showed the word TEst instead of Test, proving that this is possible.

My exploration ended there because I needed the script to be run via a Google Apps Script running on a Google Spreadsheet. However, it's been historically difficult to copy text to the clipboard via Javascript, let alone needing to do that with a custom format (not just text), and via a Google Apps Script (rather than using the HTML DOM and the Clipboard API).

Had I continued down this path, I would do the following:

1. In Google Sites, create a "template" of what I want to paste with dummy values (e.g. Title goes here, Subtitle goes here, etc.).
2. Replace those dummy values with the real values.

3. Deploy it on one of these platforms:

   • macOS application
   • Standalone webpage with query parameters (e.g. http://example.com/automate-google-sites?title=My+title&subtitle=FooBar), that could then be launched via a Google Apps Script (or possibly even embedded as an iFrame).

Since there is no easy to use API on Google Apps Scripting, you may find the maintenance cost of either of those solutions to be too much, and just hope that the new Google Sites gets App Scripting support.