{"id":10439,"date":"2018-08-16T12:13:42","date_gmt":"2018-08-16T09:13:42","guid":{"rendered":"https:\/\/railsware.com\/blog\/?p=10439"},"modified":"2022-05-10T00:16:20","modified_gmt":"2022-05-09T21:16:20","slug":"google-apps-script-gotchas-to-develop-an-add%e2%80%91on","status":"publish","type":"post","link":"https:\/\/railsware.com\/blog\/google-apps-script-gotchas-to-develop-an-add%e2%80%91on\/","title":{"rendered":"Google Apps Script Gotchas You Should Know to Develop an Add\u2011on"},"content":{"rendered":"

If you are reading this blog post, you are probably working on a Google add-on in Google Apps Script<\/a>. In this article, I want to share the specific domain knowledge our Railsware development team has gained, as well as the best practices we applied when building the Coupler.io<\/a> Google Sheets add-on.<\/p>\nAt early stages Coupler.io was a Google Sheets add-on used to automate the data import process from Airtable to Google spreadsheets. At some point in time users started complaining about API instability, low speed of work and unstable background sync. It is worth mentioning that back then the product was available on the G Suite Marketplace for free. After analyzing the support tickets, we saw clearly that Airtable users were ready to pay for a more stable version, which pushed us to basically rethink the strategy for this product.\n\nTherefore, below I will focus on the problems we faced and solutions we discovered while improving the add-on stability and integrating the ChargeBee<\/a> payment system. In any case, this article will also be useful for those who want to learn Google Apps Script.\n\n\"Using\n

1. How would you find out from the Google Apps Script spreadsheet sidebar what an active sheet is?<\/h3>\nThere is no event in the Google Apps Script API that would allow you to find out from the sidebar what an active sheet is.\n\nSo, if you want to display the relevant information in the spreadsheet sidebar based on a current active sheet – you have to write a callback function explicitly to do that job.\n\nFirst of all, write the following function in the back end:\n
function getCurrentSheetId(){\n  return SpreadsheetApp.getActiveSheet().getSheetId();\n}\n<\/pre>\nThen call that function explicitly from the Google Apps Script UI side using a helper function:\n
var currentSheetId = null;\nfunction onCurrentSheetChange(callback){\n    google.script.run\n      .withSuccessHandler(function(sheetId){\n        if(currentSheetId !== sheetId){\n          currentSheetId = sheetId;\n          callback(sheetId);\n        }\n        setTimeout(onCurrentSheetChange.bind(null, callback), 100); \/\/ time in milliseconds to wait\n      })\n      .withFailureHandler(function() {\n        setTimeout(onCurrentSheetChange.bind(null, callback), 100); \/\/ Still continue calling even if error happens\n      }).getCurrentSheetId();\n  }\n<\/pre>\nThe only way to know that the active sheet has been changed is to monitor the add-on\u2019s back end for some period of time (for example, 100ms). Of course, you will experience some delay between the time when a user changes the sheet and the time when a callback runs. In our case, the delay was about 0.5s. After you understand the code written above, all you have to do is to add this small piece of code:\n
onCurrentSheetChange(function(sheetId){\n  \/\/ Do whatever you want\n});\n<\/pre>\nNote:<\/strong> in the majority of cases you will get the following message in the browser console:\n
Net state changed from IDLE to BUSY\nNet state changed from BUSY to IDLE\n<\/pre>\nIt is just a notification about the state of the connection to the add-on’s back end. So, if you use the above-mentioned method to be notified once a new sheet opens – you will keep seeing these messages. You can check a question<\/a> related to this topic on StackOverflow as well.\n
2. Coding Google Apps Script in your lovely IDE locally<\/h3>\nThere is a way to develop your Google Apps Script locally instead of using the online Google Apps Script editor. You can use the tool called clasp<\/a> for this purpose, which will save your time by helping you structure the code.\n\nYou can develop your add-on locally in any IDE you want and by one simple command synchronize it with Google Apps Script.\n