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.



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())
toRange.setValues(thisData.getValues());
}



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).