Tl;Dr Adapted from my answer to How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website? (also posted by me) Please spend some time learning how to use the browers developers tools so you will be able to identify if the … Read more
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
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
Based on the answer here by Cameron Roberts, you can use the eval() function on the appscript UrlFetchApp function. eval(UrlFetchApp.fetch(‘http://path.to/external/javascript.js’).getContentText());
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
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
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
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
You can do this: eval(UrlFetchApp.fetch(“my url”).getContentText()) Although it would be a lot faster/more efficient to cache the code in ScriptDb or CacheService and only fetch it fresh every 15 minutes or so.
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