Automating google sheet

Adade

New Member
Joined
Oct 16, 2020
Messages
4
Reaction score
0
Credits
45
All i need is a time function, an hourly function to get a new row every hour to insert into an excel sheet.

the table is in this format


TIME


ERR1 COUNTERR2 COUNT
00
02
03
04
 
Last edited:


Please there is this billing report i work on to update a google sheet with a daily count but now we want it hourly, when i made changes to the the function it keeps over writing on the same row until next day before it's move to the next row which means it is still using the date to move but i want it to move every hour. (for hourly counts)

see function here and assist:


=================================================

PYTHON_=/usr/local/bin/python
GSHEETS=/usr/local/bin/gsheets
sheet_name="Top_cps_billing_response_codes"

get_row_number ()
{
local start=$(date --date="$START_DATE" +%s) extra=3
local end=$(date --date="$1" +%s)
local difference=$((end-start))
QUOTIENT=$(($((difference / 86400))+extra))
return 0
}

get_row () { get_row_number "$1" && echo $QUOTIENT; }

START_DATE='2020-10-10 00' # beginning row date on the current spreadsheet
ydate=$(date --date='now -1 day' '+%Y-%m-%d')
rowval=$(get_row "$ydate")

Thank you
At the moment, your date related code is only using the date at a day level. You aren’t getting the time and looking at the hour of the day at all.

So the way it’s set up, it’s still only going to work properly if it is called once per day.

Also, I have no idea what your data looks like in your sheet. I’m not familiar with google sheets, I assume it’s just a spreadsheet like calc or the dreaded excel.

Assuming you have it set up something like this:
Code:
Date.               Time
                 00:00      01:00    .....
2020-10-05         5          3    .....

Then I would say each time the script is ran, you need to look at the days date to find the appropriate row and then look at the hour to determine the column and then put your value in the appropriate cell.

Failing that, you’ll need to explain how your data is laid out in your sheet.

Also, you seem to have included the login credentials for your database in your post in the commented out rmcs= line.

I’ve removed that line of code from my quote above. I’d recommend removing it from your post too, otherwise anybody will be able to mess with your db!
 
@wizardfromoz @arochester - tagging you here because the OP hasn't been back yet.
Can you edit the OP's post and remove the commented out line please?
Just the one that starts with #rmcs=.
It looks like it includes log-in credentials for their database - I don't imagine they'd want that information publicly visible!
 
Please see sample of the function am using here if can assist

get_row_number ()
{
local start=$(date --date="$START_DATE" +%s) extra=3
local end=$(date --date="$1" +%s)
local difference=$((end-start))
QUOTIENT=$(($((difference / 86400))+extra))
return 0
}

get_row () { get_row_number "$1" && echo $QUOTIENT; }

START_DATE=$(date --d='now' '+%H:00:00') # beginning row date on the current spreadsheet
rowval=$(get_row "$ydate")


#see how the report looks like.

I need the function to move next row (move to new row but it keep over writing in the same row)every time the script runs
0:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00
 

Members online


Latest posts

Top