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
AND doesn’t work that way with Array formulae because it ANDs the whole array together in the top left cell, regardless of number of dimensions. I.e. it checks if “”>”” which is FALSE, ANDed with anything it will return FALSE for the top left cell, that result is carried down. You can use multiplication of … Read more
Seems to work for me without the StringIO: test = pd.read_csv(‘https://docs.google.com/spreadsheets/d/’ + ‘0Ak1ecr7i0wotdGJmTURJRnZLYlV3M2daNTRubTdwTXc’ + ‘/export?gid=0&format=csv’, # Set first column as rownames in data frame index_col=0, # Parse column values to datetime parse_dates=[‘Quradate’] ) test.head(5) # Same result as @TomAugspurger BTW, including the ?gid= enables importing different sheets, find the gid in the URL.
To solve this issue, try to: Create a service account: https://console.developers.google.com/iam-admin/serviceaccounts/ In options, create a key: this key is your usual client_secret.json – use it the same way Make the role owner for the service account (Member name = service account ID = service account email ex: thomasapp@appname-201813.iam.gserviceaccount.com Copy the email address of your service … Read more
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
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
I’ve tweaked @Mogsdad code slightly to print the entire spreadsheet as one PDF. The key is tweaking the export parameter. Basically replace ‘&gid=’ + sheet.getSheetId() //the sheet’s Id with (optSheetId ? (‘&gid=’ + sheet.getSheetId()) : (‘&id=’ + ss.getId())) // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided So the code … Read more
NOTE: This solution works for copying rows from one sheet to another sheet in the SAME spreadsheet, and does NOT work for copying a row from a sheet to a DIFFERENT spreadsheet. Check out the documentation here: http://code.google.com/googleapps/appsscript/service_spreadsheet.html Let’s assume you’re working in the spreadsheet where you’re copying from. You’d have to get a handle … Read more