How to sync data using Google Apps Script in Google Sheets

In episode 12, I talked about how you can use the IMPORTRANGE() function in Google Sheets to import data form a source Google Sheet to a target Google Sheet. In this episode, I get a little more technical and discuss how you can import data using Google Apps Script. If you want to read the full blog post on how to do one-way syncing, check out this blog post I wrote on the Coda blog.

sync data google sheets google apps script
Source: Google Apps Script

The script for syncing your Google Sheets

Check out this Gist to see the full script — it’s 17 lines long. Here’s the script:

var sourceSpreadsheetID = "TO UPDATE";
var sourceWorksheetName = "TO UPDATE";
var targetSpreadsheetID = "TO UPDATE";
var targetWorksheetName = "TO UPDATE";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  //Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
  //var thisData = thisSpreadsheet.getRangeByName("teamBugs");

  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())

You’ll notice that the only lines you need to edit are lines 1-4 in the script. As long as you have your Google Sheets IDs and sheet name from your source and target docs, you are good to go! I would recommend using Named Ranges in your Google Sheet to allow for situations where you data in your source Google Sheet is dynamic.

Being a maker, millennial burnout, and Google Sheets as a database

Normally I just link to the other podcasts and blog posts I comment on in the 2nd half of each episode, but I think it is worth writing a bit about the other podcasts and blog posts this time.

Adam Savage’s interview on The Tim Ferris Show embodies the spirit of being a maker. I think it’s easy to be a maker these days in the software/digital space, but there is a whole world out there of makers who are making with physical products. I would definitely give this episode a listen as well as checking out Adam’s book Every Tool’s A Hammer.

Source: Norman Chan

In the Product Hunt Radio episode with Danielle Morrill, I was reminded of this article about millennial burnout. I don’t fully agree with the character traits imbued upon millennials by the media. This article, however, did have some nuggets that I do agree with (such as the bit about finances). At a higher perspective, Danielle also discusses how we (millennials) find our community and purpose through our work instead of through traditional community, civic, and religious groups from decades before.

Finally, back to spreadsheets, Eric’s blog post about spreadsheets not being a database is one of the better articles I’ve seen about this topic geared towards non-technical folks. Check out the talk he gave at Cloud Next 2019.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting: