This works well In Google Docs : Tools -> Open Script Editor and save this script function onOpen() { var ui = DocumentApp.getUi(); // Or FormApp or SpreadsheetApp. ui.createMenu(‘Custom Menu’) .addItem(‘Insert Date’, ‘insertDate’) .addToUi(); } function insertDate() { var cursor = DocumentApp.getActiveDocument().getCursor(); if (cursor) { // Attempt to insert text at the cursor position. If … Read more
Generate UUID You can generate a UUID using Utilities.getUuid(). But it is required to use a custom function because there are no functions for it in Google Sheet’s set of functions. In order to generate UUID, please do the following: Open the Google Apps Script editor. Copy and paste the following script and save it. … Read more
Important Tip: Create multiple copies of your entire spreadsheet as you experiment. I have had 3 google spreadsheets corrupted and rendered completely in-accessible (stuck in a refresh loop). This has happened when I was experimenting with custom functions so YOU HAVE BEEN WARNED! You will want to try one or many of the following ways … Read more
Short Answer Publish your script as an editor add-on or as a Google Workspace add-on. Bear in mind that you could make it private, by selecting unlisted or making it available only for your G Suite / Google Workspace organization. Explanation Add-ons were added on 2014. This is better than using a library because there … Read more
No need to extact the value to determine if the cell is empty. Google Spreadsheet API already has a method for this: Range – isBlank method var cell = r.getCell(rws-1, 10); if (cell.isBlank()) { cell.setValue(“foo”); }
Google released API for this: https://developers.google.com/apps-script/reference/forms/ This service allows scripts to create, access, and modify Google Forms.
Short answer There is no built-in function but you could build a custom function. Example /** * Converts a datetime string to a datetime string in a targe timezone. * *@param {“October 29, 2016 1:00 PM CDT”} datetimeString Date, time and timezone. *@param {“Timezone”} timeZone Target timezone *@param {“YYYY-MM-dd hh:mm a z”} Datetime format *@customfunction … Read more
Finally found the answer. It’s pretty stupid, but apparently saving and republishing your google script web app is not good enough. You need to save a new version and publish the new version to make sure your app gets updated properly.
Utilities.sleep(milliseconds) creates a ‘pause’ in program execution, meaning it does nothing during the number of milliseconds you ask. It surely slows down your whole process and you shouldn’t use it between function calls. There are a few exceptions though, at least that one that I know : in SpreadsheetApp when you want to remove a … Read more
You can use something like this : function getSheetById(id) { return SpreadsheetApp.getActive().getSheets().filter( function(s) {return s.getSheetId() === id;} )[0]; } var sheet = getSheetById(123456789); And then to find the sheet ID to use for the active sheet, run this and check the Logs or use the debugger. function getActiveSheetId(){ var id = SpreadsheetApp.getActiveSheet().getSheetId(); Logger.log(id.toString()); return id; … Read more