A Script to Simplify Creating a SO Table

This code allows you to copy data from your spreadsheet, redact it, align each column independently and then post it in to SO with the appropriate markdown to make a nice looking table. The Code: redact.gs: function onOpen() { menu(); } function menu() { SpreadsheetApp.getUi().createMenu(‘My Tools’) .addItem(‘Authenticate’,’authenticate’) .addItem(‘Redactable Table’,’showRedactTableDialog’) .addToUi(); } function authenticate() { //no … Read more

How to get form values in the submit event handler?

There are 2 patterns for retrieving submitted values. For both patterns, the function for retrieving the values from form submission has to be installed as a trigger. The detail information of Installable Triggers is https://developers.google.com/apps-script/guides/triggers/installable. 1. Script is opened on spreadsheet. In this case, by installing a trigger, you can retrieve the submitted values by … Read more

How to Autofill last row with formula, when data is received from IFTTT on the last row?

This is a very common problem when dealing with auto-inserted data through sheets api or Google forms. The easiest solution would be to convert all your formulas into arrayformulas. For eg, Your formula in D2 =MID(C2,1,2)&”.”&MID(C2,3,2)&”.2020 can be modified as In D1: =ARRAYFORMULA({“Extracted Date”;MID(C2:INDEX(C:C,COUNTA(C:C)),1,2)&”.”&MID(C2:INDEX(C:C,COUNTA(C:C)),3,2)&”.2020″}) OR using regex: =ARRAYFORMULA({“Extracted Date”;REGEXREPLACE(C2:INDEX(C:C,COUNTA(C:C)),”(\d{2})(\d{2}).*”,”$1.$2.2020″)}) The table then becomes: | | … Read more

copy data from one sheet to another in google app script and append a row, one small issue

You can copy whole range at once using copyTo, so your function could be rewritten as: function copyInfo() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var copySheet = ss.getSheetByName(“Copy”); var pasteSheet = ss.getSheetByName(“Paste”); // get source range var source = copySheet.getRange(2,2,12,2); // get destination range var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2); // copy values to destination range source.copyTo(destination); // … Read more

ScriptApp.getService().getUrl() points to dev URL. How can I get it to point to exec production URL?

How about this answer? Please think of this as just one of several possible answers. Issue and workaround: If you are enabling V8 runtime, in the current stage, it seems that when V8 runtime is enabled, ScriptApp.getService().getUrl() returns the dev URL like https://script.google.com/macros/s/###/dev, and when V8 runtime is disabled, it returns the exec URL like … Read more

Google script replaceAllShapesWithImage with image from drive doesn”t work any more

How about this answer? Please think of this as just one of several possible answers. Issue and workaround: I think that the reason of your issue is due to the following modification of official document. First, we’re making changes to authorization for the Google Drive API. If you authorize download requests to the Drive API … Read more

Google Apps Script toast messages don’t appear for anonymous editors

You want to display a message when the cells of Spreadsheet are edited by anonymous. The Spreadsheet is publicly shared for anonymous users as the editor. If my understanding is correct, how about this answer? Unfortunately, even when the installed OnEdit event trigger is used, when anonymous users are edited, toast() and Class Ui cannot … Read more