Newbe Dev Stack

Answer : API key is not enough. You're trying to use spreadsheets.values.append which requires OAuth authorization: Authorization Requires one of the following OAuth scopes: https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/spreadsheets For more information, see the [Auth Guide](https://developers.google.com/identity/protocols/OAuth2). Note it says OAuth. You could use the format as below: https://docs.google.com/spreadsheets/d/{sheetID}/export?format=csv Make a URLConnection to this URL, after replacing the sheetID with your public sheet and read the csv file as you would normally do in your programming language of choice. Please note that this is only true for Readable Public Google spreadsheets, and is a bit of a hack when you don't necessarily need to do the Credentials jig and jive In my case I need to publish content from a Google Spreadsheet to a web page. My workaround consists in u...

Answer : When you create a menu item with .addItem('Nathaniel MacIver', menuItem2('nm@emailaddress.com')) the function menuItem2 is called with the parameter 'nm@emailaddress.com'. This results in the alert that you see. The return value of the function is undefined (as you don't return anything from it). So you end up with the same menu item as if it was .addItem('Nathaniel MacIver', undefined) which clearly isn't going to do anything. The method addItem takes only a function name, it does not allow for passing parameters to that function. To do what you want, you'll need separate functions for each person, each with an email hardcoded inside that function. Although you can't pass functions that invoke with a variable directly through the .addItem() method, because it only accepts strings, you can take a couple of extra steps (extra steps relative to what you were trying to do) to dynamically create functions that are pre-s...

Answer : The , at portion of the string is keeping Google Sheets from recognizing it as a datevalue. Just remove it with the substitute function and wrap in datevalue function like so: =DATEVALUE(SUBSTITUTE(A1,", at","")) To format as DD/MM/YYYY just go to custom formatting and set it to look like the following: =DATEVALUE(JOIN("/", LEFT(D5,2), {MID(D5,4,2), RIGHT(D5,4)})) where D5 contains for example: 25.06.2019 which script converts to datevalue: 43641 Dateformat is as dd.MM.YYYY converted to dd/MM/YYYY and then converted to datevalue. Google sheet's documentation helps: DATEVALUE , JOIN , LEFT , MID , RIGHT Datevalue is useful for organizing rows of data by date correctly. Another solution is to create custom function. Open tools → script editor in menu to open script editor in new tab Click Untitled project in top left corner and rename Open Resources → Libraries in top menu Paste library key MHMchiX6c1bwSqGM1PZiW_Pxh...

Answer : As on July 2018, Apps-Script support changing individual text colors and other font related styles. Two methods are added to SpreadsheetApp . newTextStyle() and newRichTextValue() . The following apps-script changes such fontstyles in A1. For best effects, Use a lengthy string(30 characters or more). function rainbow(){ var rng = SpreadsheetApp.getActiveSheet().getRange("A1"); var val = rng.getValue().toString(); var len = val.length; // length of string in A1 var rich = SpreadsheetApp.newRichTextValue(); //new RichText rich.setText(val); //Set Text value in A1 to RichText as base for (var i=0;i<len;i++){ //Loop through each character var style = SpreadsheetApp.newTextStyle(); // Create a new text style for each character var red= ("0"+Math.round((1/len)*(i)*255).toString(16)).substr(-2,2); // var green= ("0"+Math.round((1/len)*Math.min(i*2,len-Math.abs(i*2-len))*255).toString(16)).substr(-2,2); // var blue= (...

Answer : You should be able to use a vertical array in the Spreadsheet 3 : ={IMPORTRANGE("Sheet1Key","SheetName!A2:A500");IMPORTRANGE("Sheet2Key","SheetName!A2:A500")} Of course, it is also possible to combine several IMPORTRANGE() functions with the QUERY() function , which gives us a greater control over the results we import . For example, we can use such a construction : =QUERY( { IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100"); IMPORTRANGE("key-or-url-of-spreadsheet-2", "'sheet-name-2'!A2:Z100"); IMPORTRANGE("key-or-url-of-spreadsheet-3", "'sheet-name-3'!A2:Z100"); IMPORTRANGE("key-or-url-of-spreadsheet-4", "'sheet-name-4'!A2:Z100") }, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC" ) Explanation: The above query removes blank lines from imported ranges : SE...