How to Share a Link to old revision in Google Sheets?

There are scenarios when you don't want to share you live edit to your audiences in a shared Google Sheets because you are not yet to publish the draft. While you may say why not share a PDF file or Excel file snapshot to your audience every update, or share a link to a copy of google sheet, the user experience is not ideal and your productivity suffers. This article demonstrates a new way to share a link of your old revision in Google Sheets and allow you to control when your audience sees your work updates.

How it works

Okay, let me explain. First of all, Google doesn't provide a mechanism to actually link to old revisions, so this solution is a hack.

The solution takes 2 sheets, one internal sheet you are editing and drafting, and a public read-only sheet for your audience. The solution is an apps script that runs on your internal sheet. When you are done with your draft and ready to publish to your audience, run the apps script to copy all sheets to the public sheet. You audience will see all work updates at once. Thus, the public sheet is like your internal sheet's old revision.

Take a look at this demo.

Tap into the code

To load the solution on your own sheet, do these

1. Create an empty read-only public sheet that you will share to your audience. Note down the sheet id, it's in the address bar.
2. Copy the following apps script to the script editor of your current internal editing sheet. If you don't know how to do it, follow my another article How To Add An Apps Script To Your Google Apps?
3. Now replace the sheet id in the script with your public sheet id in step 1.
4. Now refresh your internal sheet. You will see a Publish > Publish menu coming out. Click that to share your gold work with your audience.

/**
 * @license
 * The source code is subject to the terms of service in
 * https://xfanatical.com/terms-of-service/#open-source
 * 
 * © 2019 xfanatical. All rights reserved.
 */
 
// Change this string to your public sheet's id
var publicSpreadsheetId = '1z5TGk9bYHl4KjyMtFoTN8jfxuPk5g1fhBYUbZJdoFnU'

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Publish')
  .addItem('Publish', 'publish')
  .addToUi()
}

function publish() {
  var ssInternal = SpreadsheetApp.getActiveSpreadsheet()
  var ssPublic = SpreadsheetApp.openById(publicSpreadsheetId)
  
  var sheetsInternal = ssInternal.getSheets()
  var sheetsPublic = ssPublic.getSheets()
  
  for (var i = 0; i < sheetsPublic.length; i++) {
    var sheet = sheetsPublic[i]
    sheet.setName(sheet.getName() + '-tmp')
  }
  
  for (var i = 0; i < sheetsInternal.length; i++) {
    var sheet = sheetsInternal[i]
    var newSheet = sheet.copyTo(ssPublic)
    newSheet.setName(sheet.getName())
  }
  
  for (var i = 0; i < sheetsPublic.length; i++) {
    var sheet = sheetsPublic[i]
    ssPublic.deleteSheet(sheet)
  }
}

Thanks for reading. Feel free to leave any comments.