Googlesheet api

Hi,

I am building an app for my small business and would appreciate some advice.

The app is designed to let my employees log their work hours daily. These hours will be stored in a Google Sheet, and at the end of the month, I’ll use this data to calculate their monthly pay.

The app consists of three screens:

  1. Login Screen
  2. Dashboard: Displays the total hours worked so far.
  3. Update Hours: Allows employees to submit their hours for the day.

I’ve successfully created the login screen and linked each user to a dedicated Google Sheet.

However, I’m struggling to set up the web API to interact with this Google Sheet. I’ve spent the entire day on this without success.

Does anyone have experience with a similar project?

Thanks in advance for your interest!

Nico

Is there a reason you’re wanting to use the API instead of the built-in data source blocks?

What exactly are you wanting to do? What is an example of a specific function?

1 Like

Hi,

The reason why i want to use the api, is that I don’t know in advance what file will be used. I only know once the user has logged in.

But maybe I m wrong, I just don’t understand how I can do it with data blocks

Thx for your intrest

Sounds like a great idea. Is this currently a system you have up and running internally or are you starting from scratch? (i.e. are you simply adding a mobile interface to your existing spreadsheet-powered workflow?)

If everyone logs their hours in the same spreadsheet then you can use our Google Sheets integration.

If everyone has their own sheet then you’d need to use the API approach - but this begs the question then, how do employees submit their hours to you/payroll?

Hi,

I m starting from scratch.

I actually didn’t think of logging everyone’s hour on the same sheet, that could be a solution. Then I could just sort out by name later in googlesheet…

However. My original plan is to have a spreadsheet for each of them. I m finding their spreadsheet s url through the data block using a central spreadsheet where everyone has his id associated with the spreadsheet url.

Sorry I m not native English I hope I m clear in my explanations…

Thanks for your interest

Nico

Makes sense - how many employees do you have? What are you expecting to grow to by the end of the year?

The only problem I have with the one-sheet-per-employee is that it doesn’t really scale very elegantly as new employees join/leave and if you double the number of employees then your Admin will have to do twice as much leg work to reconcile all the data at the end of each pay period.


I’d go with one sheet for all the raw data and then you can create additional sheets in the same workbook to filter per employee, or a dashboard view to show most recent data etc.

Hi,

This one workbook idea sounds great! However I still have a similar problem, how do I change sheet ?
I don’t understand how to use the blocks to switch the source of data accordingly to the user …

that’s the great thing - you don’t need to change the sheet.

Each new row is a new record, with date, employee name, hours worked etc and then you just set up the filter you need on a second sheet in that workbook to display the high level data your admin needs.

The sheet “database” is never shared with the employees to keep their data private, they can only interact with it via your Thunkable app.


NB - this is important. If it’s a large enough company then you are probably better off skipping straight to a proper database.

Yessssss!!! Of course! How did I miss that! This is wonderful thank you so much This is going to save me so much time!!!

We are a small company so I sure it will be fine.

Awesome, I try this tonight, let you know how it went.

Thank you again!

1 Like

Hi,

i have only 7 employees

I started screen 2 from the begining since i have made a huge mess trying to use API… but of course i m blocked again…

I m at the stage where i want to identify the user, linking it s email to it s name. but somehow the in list block doesn t seem to find the item in the list … i can t find what i do wrong… can you help?
screen below :

Thanks again for your help

Hello @nicolas.abelanetf6
The “list of values” data source block is already a list so you don’t need to use the block “list” to connect it with the “does list contain” block

amazing!!! thank you so much

1 Like

Yep - you just have a list inside a list.

This purple list of values block returns all of column B in your spreadsheet as a list already.

You are then “wrapping” that list as the first item in another list with your blue + list - block

Simply remove the blue block (and use the purple block only) and it will work for you.


Going forward, this might be a helpful resource to have too:

Hi everyone,
thank you so much for all your advices and help, my app works fine!! i m hopping i can start using it next week!

however i m fine tuning it adding littledetails here and there, and i enconter new difficulties… somehow the easiest things dont work anymore, and as i m verynew to all this i can t see what i m missing…

can somme one have a look at that screen: i d like to output the username but somehow it doesn t work!

the label s text doesn t change

thanks in advance for your precious help

nico

Two observations here Nicolas.

  1. In the previous screenshot you share of your spreadsheet the name column was mostly empty. Have you added in data to this column since this?
  2. We generally recommend, for performance reasons, against using more than one get value block. If you need two or more values from a row use the get row object block. Full instructions on how to do this can be found here:

Hope that helps!

How’s it going for you @nicolas.abelanetf6?

@nicolas.abelanetf6 Check this Guide out!! CRUD Method for Private/Public Google Sheets - Resources - Kodular Community also using an api has limited amount of requests and this method by me does waymore than that with almost no limit (All you need is a web component)

Hi
thanks for asking.

i had taken a break on this.

now i m looking for a way to get my user to download a file from url to his device. is this possible?

thanks in advance

Hello @nicolas.abelanetf6
You could use the download blocks to download a file from a URL onto your device and use it offline (This file cannot be accessed from outside the app). More information in our docs: Files Blocks | Thunkable Docs