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

Getting Google Spreadsheet CSV into A Pandas Dataframe

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.

Google Sheets API returns “The caller does not have permission” when using server key

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

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

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

Convert all sheets to PDF with Google Apps Script

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

How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?

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