I thought that I would throw in a similar issue that I had which brought me to this question, where I received the error You don’t have permission to call by openById. In my case I was trying to call functions from translate.gs which I copied from this example: https://developers.google.com/apps-script/quickstart/docs Note that at the top … Read more
The following function retries the value in a column with a given name, in a given row. function getByName(colName, row) { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues(); var col = data[0].indexOf(colName); if (col != -1) { return data[row-1][col]; } } Specifically, var col = data[0].indexOf(colName); looks up the given name in the top … Read more
You can programmatically import data from a csv file in your Drive into an existing Google Sheet using Google Apps Script, replacing/appending data as needed. Below is some sample code. It assumes that: a) you have a designated folder in your Drive where the CSV file is saved/uploaded to; b) the CSV file is named … Read more
I can suggest a simple solution without using a script !! Lets say you want to delete rows with empty text in column C. Sort the data (Data Menu -> Sort sheet by column C, A->Z) in the sheet w.r.t column C, so all your empty text rows will be available together. Just select those … Read more
Here are some of the parameters I found for Google Docs (thanks goes to Joel http://obstruction.tumblr.com/post/60784440737/google-docs-url-parameters-rm-minimal-rm-full): Google Docs URL parameters: rm=minimal rm=full rm=embedded rm=demo rm=(render mode) ui=2 (select the interface version) chrome=false (full screen mode) frameborder=(size of border) q=(Whatever) Search Query gid=24 (Which sheet you want to display) widget=false single=true range=A2:AA26 Output=html format=(export spreadsheet) format=xlsx … Read more
No, there’s no equivalent to Excel’s EVALUATE() in Google Sheets. There’s long history behind this one, see this old post for instance. If you’re just interested in simple math (as shown in your question), that can be done easily with a custom function. function doMath( formula ) { // Strip leading “=” if there if … Read more
Let’s say your arrays are: A1:A3 = {1;2;3} and B1:B3 = {4;5;6} Write somewhere: ={A1:A3;B1:B3} Semicolons ; are used to separate rows, and commas , are for columns. Here is the documentation: Using arrays in Google Sheets
Generate UUID You can generate a UUID using Utilities.getUuid(). But it is required to use a custom function because there are no functions for it in Google Sheet’s set of functions. In order to generate UUID, please do the following: Open the Google Apps Script editor. Copy and paste the following script and save it. … Read more
Important Tip: Create multiple copies of your entire spreadsheet as you experiment. I have had 3 google spreadsheets corrupted and rendered completely in-accessible (stuck in a refresh loop). This has happened when I was experimenting with custom functions so YOU HAVE BEEN WARNED! You will want to try one or many of the following ways … Read more
To convert duration to an integer expressing the number of seconds, use a formula such as =value(A1*24*3600) Time values are recorded so that 1 is one day. Multiplying by 24 (hours/day) and 3600 (seconds/hour) converts that to seconds. Then value makes it a number rather than duration. Old answer, about formatting only. You don’t need … Read more