Array of Integers in Google Spreadsheets

I was working on a Google Spreadsheet today, and I needed to generate a list of integers to use in conjunction with a built in function. I actually needed to find the gradient of different (one-dimensional) array.

The problem was, the "SLOPE" function built into Spreadsheets requires both x and y data (at least at the time of writing). More info on that can be found on their official help page for SLOPE. However, I just had one set of data (i.e. just the y values), and I didn't want to display a list of integers in the spreadsheet, just for aesthetic reasons.

On this note it would have been really useful if the y-data argument supplied to SLOPE could have been optional (perhaps defaulting to [1, 2, 3, ... ] if only one array is supplied?). It seems there is no way of doing this using SLOPE so I needed to generate a list of integers, on the fly, with exactly the same number of elements as there are in the array I am supplying as y data.

It turns out this is actually quite hard to do in Google Spreadsheets - there is certainly no built-in way to do it. After googling the problem for a while I still couldn't really find a good way to do it, but I did come up with something a bit hacky as a workaround. Seeing as I failed to do it any other way, I thought I would post my method here!

---

How to generate a list of integers

Here is what I used to generate a list of integers from 1 to 10:

ARRAY_CONSTRAIN(ARRAYFORMULA(ROW(INDEX(A1:A))), 10,1)

Essentially what the formula does is to use the row numbers in the spreadsheet itself, and generates a list of integers from that. In other words, it converts [A1, A2, A3, ... ] into [1, 2, 3, ... ]. If you change the '10' supplied in the formula above, it will change the length of the list of integers.

The one drawback to this method is that it will only generate a maximum length equal to the number of rows in your spreadsheet. If there are only 30 rows in your spreadsheet then it will only be able to create a list of integers up to 30. If you run into this problem, then you may be able to use the columns instead of the rows, using the column numbers to source the list of integers instead:

ARRAY_CONSTRAIN(ARRAYFORMULA(COLUMN(INDEX(A1:1))), 1,10)

Again, you can change the '10' in the formula above to whatever number you need.

---

Extra tips:

• Use the TRANSPOSE function if you need to change the orientation of the array generated. This will change a vertical list (i.e. one that fills several rows in one column) into a horizontal list (i.e. one that displays within one row).
  
• Use the COUNT function if you need to make your list of integers match the length of another array. Replace the '10' (in the code above) with COUNT(requiredarray). I actually need to use this in the sheet that prompted this whole blog post. I needed to create an array of integers to act as x-values, to match a variable array of y-values, in order to find the trend (the gradient) of those y-values.
  

Anyway, that's it! I hope this is useful to somebody out there.

Kivy touch screen issues (SOLVED)

Recently I have started messing around with the amazing Kivy. If you've not heard of it, it's a relatively easy way to write apps in python that will run on Android, iOS, OS X and Linux (without changing your code for each one). If you have heard of it already, it's also that. The best resources I've found for learning it so far have been Kivy's own Pong tutorial, and Alexander Taylor's excellent video guides.

From time to time I'll post things here that I pick up which I found useful, and the first of these is a little touch screen issue which I managed to resolve. I am trying to create a simple game, and I just want one input. Every time the player touches the screen it moves a balloon downwards, kind of like a jump in many other games. So I needed a touch screen input.

Kivy's Pong tutorial uses the built-in function "on_touch_move", so this seemed like a natural place for me to start, using this code in my main.py python file:

def on_touch_move(self, touch):
 self.avatar.jump()

Seems simple enough, right? This calls the function "self.avatar.jump", to make the avatar jump, whenever the screen is pressed. The problem I had (on both my laptop and my Android phone) is that its functionality is quite intermittent. Sometimes the screen would register my press, and sometimes it wouldn't. This is obviously not good enough for a game that relies on reactions, good timing and a single user input. The "on_touch_move" function also registers the position of any screen touch, and perhaps this is slowing things down. All I needed was the fact that the screen was touched.

To get around this issue you can use a button instead of the "on_touch_move" function. Whenever this button is pressed, a specific function is called. You can also make this button fill the screen, and make it transparent, so that we have the functionality I wanted. To use this there is actually no extra code in the main.py python file, and instead it is held in the .kv file:

Button:
 height: self.parent.height
 width: self.parent.width
 background_color: (0, 0, 0, 0)
 on_press: root.avatar.jump() 

The first line obviously just tells kivy we are making a button. The next two set the height and width of the button: here we have made it inherit its parent's size, so as to make it fill the screen. The last element of the background_color setting (the last 0 in (0, 0, 0, 0)) is the alpha, i.e. transparency. 1 would have been totally opaque, and is the default, so we must set this to 0 to be transparent. The final line tells the button what function to trigger. This has to be a function held within the main.py file, obviously.

That's it! I was really worried I would have to give up on kivy altogether when I found this intermittent and unreliable touch screen problem, which would have been a real shame as it has huge potential. However, my faith has been restored.

Hello World on Heroku (with Python)

When I first heard about heroku it sounded amazing, but when I tried to use it I felt quite deflated, as I found it really hard to use. So once I got a Hello World working I thought I would write a tutorial to help anyone else out there. Please note, I use ubuntu, so some things will be a little different if you use another OS.

Wait, what is heroku?

Heroku is basically a web hosting platform, but it is one that is set up so that you can (in theory) easily deploy web apps using a variety of languages and, importantly, scale them should they become successful. However, for a cheapskate like myself, one of the main selling points is that before your web app gets successful, it is free!

---

Step 1: Creating an app

There are two ways of creating an app: from the command line; or through the heroku website. If you’re reading this article you probably want the easy way! There is a good tutorial on the heroku website regarding creating an app from the command line , and then renaming it; but it is probably much easier to do it through the website: go to your dashboard and click on “Create a new app”.

---

Step 2: Install heroku toolbelt and "Heroku Push"

Download and install the heroku toolbelt from toolbelt.heroku.com. This is a set of tools that make it much easier for your local computer to talk to the heroku website using the command line. It will make the next steps much easier. You can do this from the command line by using:

wget -qO- https://toolbelt.heroku.com/install-ubuntu.sh | sh

"Heroku Push" is a fricking brilliant tool which allows you to publish things to heroku without using git. Insulting git seems to generate a torrent of hatred from purists out there, but I am afraid I have found it nothing but confusing! Actually, given how much love for git there is out there, it must have something going for it, but I think it is much more useful for several people working on the same complex project; I am a solo developer trying to deploy fairly simple apps, and would much rather not use it. There, I said it.

Go to the git page for heroku-push for more information (OK, you have to use git to get this tool!), but you can just install it from the command line:

heroku plugins:install https://github.com/ddollar/heroku-push

Later on, I will recommend using the command "heroku push" from the command line - this will not work without installing this plugin.

---

Step 3: Assemble the app’s files

There are three files that you need in order to make this work. I would recommend keeping these files in a folder together somewhere, i.e. a folder within the directory you are using for this project:

• requirements.txt
• Procfile
• hello.py

requirements.txt

The requirements.txt file tells heroku which plugins it needs to load in order for your app to work. I have played around a bit with including some things and leaving some out, such that I think that what I've put here to include is the minimum needed in order for your python code to work, The flask framework depends on the Jinja2 and Werkzeug libraries, and the gunicorn plays the part of the server. Save the following four lines in your requirements.txt file:

Flask==0.9
Jinja2==2.6
Werkzeug==0.8.3
gunicorn==0.17.2

Procfile

Save a file (without an extension) named “Procfile”, with the following contents:

web: gunicorn hello:app

While I must admit I have nigh-on zero experience of using gunicorn, I believe that it is a rudimentary web server called by heroku, which is in turn told what files to serve. So the “hello:app” part tells gunicorn to load the right python file. You can put anything you like here, but it must have the same name as the python file. In other words, you could put "web: gunicorn foobar:app", but you would have to save your python file as "foobar.py".

Python file (hello.py)

Note that this can be called anything, but must be a python file. In other words, it must have the python file extension (“.py”), but could be mygreatapp.py, or even myrubbishapp.py, or whatever.py. However (see above), your Procfile would have to change to reflect this.

from flask import Flask

app = Flask(__name__)

@app.route('/')
def source():
 html = 'Hello World!'
 return html

Obviously we have defined a function, “source()” within this python file (this function can be called whatever you want). From what I can tell, the first function defined in the file is called automatically, but none below that. I’m not 100% sure, but I believe that any normal python will work within this function (I’ve not really played around with heroku much yet, so there may be limitations). It seems like anything returned by the function (i.e. the final line) is printed out to the webpage, effectively being the html source file.

---

Step 4: Deploy your app to heroku

Open the terminal (as I mentioned, I’m using ubuntu - I’m not sure how you do this with any other OS, but what we are trying to achieve is to upload the working directory to heroku). First, log in to heroku using the following command (entering your email and password at the prompt):

heroku login

Change to the directory where the files (defined above) are held.

cd path/to/project/directory

Deploy your app to heroku using the following command, making sure to change the relevant part to the name of your app:

heroku push --app name-of-app

For example, my app here is called "octomaton-hello-world" (see the picture in Step 1 above), so I would enter "heroku push --app octomaton-hello-world". Go and make yourself a cup of tea: it could take a good couple of minutes for the app to deploy.

Finally, go to your app's url to test it; for this app it is octomaton-hello-world.herokuapp.com. Hopefully it should be saying hello!