personal finance Archives

I am a long time user and fan of Wise, since it was called Transferwise. I even used it to transfer money from Italy to Hungary (€->HUF) when I got married, so I’m emotionally attached to it.

It’s cheap, reliable, the app works fine and the site is simple and fast. <plug> Go sign up and earn us both a reward. </plug>

Anyway, one my few gripes with it is that I keep track of my finances in Google Sheets, and Wise does not have any native integration with it.

But it does support a generic form of webhooks. And what do you know, Google Sheets support webhooks! Kinda!

Setting up a sheet to receive POST requests

It’s somewhat insane that you can make a spreadsheet the database for a web app, but it works.

First, create a new sheet. You don’t need to do this, but I feel it’s saner to have a sheet which is just a dump of events, and keep your “important” stuff in a different one.

Now go to Extensions -> Apps Script and it will start up the scripts mini-IDE. Usually you use this to add macros or some such, but you can build a web app in there!

The code you need is something like this, put it in code.gs

// yes, this code sucks, I don't care.

function doGet(e) {
  var params = JSON.stringify(e);
  addRecord([[params]]);
  // you need this or you will get an error
  return HtmlService.createHtmlOutput("ok")
}

function doPost(e) {
  // make both POST and GET work the same
  return doGet(e);
}


function addRecord(values) {
  // use this for debugging
  console.log("values", values)
  // sheet name and range where you want to put your data
  range = 'Sheet1!A:A'
  // the id you see in the browser URL bar
  const spreadsheetId = "your spreadsheet id"

  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = spreadsheetId;
    appendRequest.rows = [valueRange];

    const result = Sheets.Spreadsheets.Values.append(
      valueRange, 
      spreadsheetId,
      range, {valueInputOption: "RAW"});
    console.log('Result', result);
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

Beyond the oddity of the Google APIs, this is basically

listen to a POST request
add a new entry as JSON to a sheet

Once you have the code, hit the “Deploy” button on the top right, and choose “new deployment”.

In the modal that opens up, use the gear button to choose the kind of deployment, which should be “Web app”

Fill in the details:

set “Execute as” to your account, which will allow the app to write to your sheet
set “Who has access” to “Anyone“, so that Google will not require authentication. The paranoid person in me does not love this, but I don’t know of a way to secure this better. The URL is pretty unguessable tho.

At this point Google will deploy your web app and provide you with a URL, something like

https://script.google.com/macros/s/ABUNCHofGibberishWithL3tt3rsAndNUMB3rZ_LOLWATHAVEY0u533nThis-there4r3DaSh35t00/exec

You can even access this via a browser, and you should see the profound message that many developers have used to prove that God’s in his Heaven, all is right in the world: ok

You can also test a POST request via curl

curl -d'{"wat":"wat"}' https://script.google.com/macros/s/ABUNCHofGibberish...

If all is working correctly, you should be able to see some HTML response.

Now, if you go to the Sheet, you’ll find that there is nothing there! I tricked you, but it’s so you can learn. If you want to use any of the myriads of Google services, you need to first include it in the project.

If you go back to the Apps Script studio and check out the Executions entry in the sidebar, you will see the logs of your request, and indeed, you should have an error like

Failed with error Sheets is not defined

Notice that checking this logs is your main form of debugging, get used to logging a ton of stuff.

To fix this error, go back to the Editor, click on Services and find Google Sheets, select the V4 API, and use “Sheets” as the name.

Now you can deploy this again (create a new Deployment, and notice you get a new URL). This time, you should get an authorization screen, and since your app is not approved, you need to navigate it it so it lets you past the OAuth screen.

Now you can try to access this again via browser and curl (remember to use the new URL!) and finally you should see some records in your sheet!

Configuring Wise

In the Wise web app go to Settings -> Developer Tools -> Webhooks, and create a new one. Pick the events you want according to the documentation, put the URL of your app in there and hit the “Test webhook” button.

If all went well, you should see a new event in your sheet.

Notice that Wise does not currently allow you to edit a Webhook, so if you want to update it you need to delete it and create it again.

Feel free to send them feedback 😉

Conclusion

I had no idea that Google allows you to use Sheets as the database for a web app, and I’m honestly impressed. The debugging facilities are not that obvious, and when you get an error it’s pretty hard to understand what went wrong, but it works.

Wise webhooks are not that obvious either, and it would be nice to be able to send test requests with various payloads, rather than wait one month to see if the deposit data you’re getting matches what you expect, but it works and it’s still better than many banks.

If you use this, or something better, or have suggestions for improvements let me know in the comments. Happy hacking!

Tag: personal finance

Integrating Wise and Google Sheets

Setting up a sheet to receive POST requests

Configuring Wise

Conclusion