GoogleSheetsBilling Download 1.1

This component/theme works with latest OSSN version.
us bryce alvord 5 years ago

Google Sheets Billing is a component that allows you to add a spreadsheet of user names and their balances to a new Profile/Billing subpage for Admin users. For non-admin users, the Billing subpage will show their Name and their Balance.


  1. You need to start a google sheets document with Column A being {LastName},{FirstName}.
  2. You need Share the spreadsheet and copy the URL in edit mode
  3. You need to create a Google Project with Sheets API enabled and obtain an API Key
  4. With the component enabled, go into the Configure>>googlesheetsbilling
  5. Enter your Edit sheets URL in box 1, Your API URL Minus the ?key=... in box 2, and finally your API key in box 3 and save.

Now a standard user can go into the Billing sub page and see their balance. An admin user will see the editable sheets document in place of the balance so they can modify balances.

Known weakness is if you have two users with the same first and last name it will cause issues for sure. One possible solution is to add a second identifier like an email address to match on. That will come if needed.

German Michael Zülsdorff Replied 5 years ago

Thanks a lot, Bryce.
Wished I'd found that earlier. :)

Just a thought ... in case you don't want regular members see eacher others balances ... you may add a

if(ossn_isLoggedin() && $owner->guid == ossn_loggedin_user()->guid || ossn_isAdminLoggedin()){ ...

in profilebillinguser() to make the Billing tab disappear


if($page == 'billing' && ($params['user']->guid == ossn_loggedin_user()->guid) || ossn_isAdminLoggedin()) { ...

inside of your page handler to prevent hacking attempts of super smart member trying to enter that link manually

us Bryce alvord Replied 5 years ago

Hey Z Man,

Thanks for all the help vetting this thing. With it being my first component it really needs an extra pair of seasoned eyes. I will modify the component and only initialize one time like you suggested, that is a clean way to do it for sure.

As for the documentation, this is what I looked at to figure out how to make the calls,

I also used this API tool to test everything which made it very easy,

Hope that helps Z Man


German Michael Zülsdorff Replied 5 years ago

Re identifying the right user:
The more simpler and foolproof way is using the username instead of Lastname, Firstname because it's guaranteed to be unique and can't be changed.

German Michael Zülsdorff Replied 5 years ago

First of all: I'm able to access my sheet this way - just tried it in the browser. Many thanks!

To tell my little horror story from tonight: Since I've never used that API before I had no idea which way to add that cell range. But I remembered the line of your former posting:

$sheetsURL = "";

But basic/A1%3AB40 didn't work - returning a 404 error. So I tried it without range, and in fact I received JSON this time, returning some nice general information about my sheet, but no data. :(
Okay, wrong approach I thought - time to study the API docs. And unfortunately I landed on Actually, doing it like that returned my data for the first time

  "sheets": [
      "data": [
          "rowData": [
              "values": [
                  "effectiveValue": {
                    "stringValue": "Lips, Botox"

Yeah, and JSON decoding this baby gives a nested array you wouldn't want to process. And that's where I gave up.

I'd really appreciate if you could tell me the link to the Google page
where stuff like /values/ is documented.

Aside from that (having a look into your form):
It's not necessary (and just eating up memory) to create a new object more than once. See

It's sufficient to do a

$component = new OssnComponents; 
$settings = $component->getSettings('COMPONENT');

one time in the beginning, as $settings will be valid til the end of your code, and you can just do a $settings->setting1 ... $settings->setting2 .... $settings->settingN later inside of your html

us Bryce alvord Replied 5 years ago

Sorry I made that hard to understand, I couldnt figure out a good way to write it. So with your sheets and api project your fields should look like the following samples but with your own data. These arent working URLs as I did have to modify the api key and sheets url for security purposes but the look is the same.

Field 1 should look like this. The critical piece is the /edit?usp=sharing, make sure you add that on to your sheets url like below

Field 2 should look like this. Its mostly the same as field 1 but remove the /edit... and add /values/A1:B50 if that is the range of your data but it could be C2:D900 if thats where your data is.

Field 3 should look like this. This is a direct copy of your API key from the Google Projects API page for your Sheets credentials found at{your api project}


Let me know if that helps get you started.

German Michael Zülsdorff Replied 5 years ago

Thanks for providing this promising component, but I'm stuck on finding the correct API url.

Here you wrote: "Your API URL Minus the ?key=... in box 2"
The admin backend says: "Sheets URL w/ Cell Range"

That is: https://apiurl?ranges=RANGE ... but then it would need a &key=APIKEY in the com file, not ?key=APIKEY. I'm confused, yes. :)

Could you please help me out with an example API url?


Developer: bryce
License GPL v2
Type: Site admin
Requires Ossn Version : 4.x
Latest Version: 1.1
Last Updated 5 years ago
Repository Url View Repository