Author Topic: Google Sheets help  (Read 3029 times)

Offline talmid chuchem

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2014
  • Posts: 934
  • Total likes: 30
  • DansDeals.com Hat Tips 4
    • View Profile
Google Sheets help
« on: January 30, 2017, 01:28:39 AM »
As the world is progressing more & more towards cloud based software, I think that it's time to have a Google Sheets help thread, like there is a Microsoft Excel help  thread.

Offline talmid chuchem

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2014
  • Posts: 934
  • Total likes: 30
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Google Sheets help
« Reply #1 on: January 30, 2017, 01:36:22 AM »
I'll start: anyone with google apps script for converting Hebrew dates into Gregorian dates?

To Explain why i would need this: I would like to have a sheet with things that recur yearly on the jewish calender, & get a reinder a few days before. For example i would like to put into the spreadsheet chanukah start date, I would put in כה כסלו in the date column,  in description column will be Chanukah starts, & in reminder column will be how many days before i would like to get reminded.

Thank I'll have the script checking for כה כסלו & the year will be added in the script (as the year changes & i want to get reminded every year), & the script will calculate the Gregorian date based on the hebrew date that the script will feed it (date column & year), & then the script will check the criteria set in the third column if it should send a reminder email.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4428
  • Total likes: 205
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Google Sheets help
« Reply #2 on: January 30, 2017, 08:22:03 AM »
Why not just download something like this to your google calendar and use the calendars reminder feature for notifications?

https://www.hebcal.com/ical/

You can customize the calendar before downloading to have all your pertinent info.
Once the game is over, the king and the pawn go back in the same box.

Offline grodnoking

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Nov 2014
  • Posts: 4411
  • Total likes: 218
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Google Sheets help
« Reply #3 on: January 30, 2017, 08:25:41 AM »
I'll start: anyone with google apps script for converting Hebrew dates into Gregorian dates?

To Explain why i would need this: I would like to have a sheet with things that recur yearly on the jewish calender, & get a reinder a few days before. For example i would like to put into the spreadsheet chanukah start date, I would put in כה כסלו in the date column,  in description column will be Chanukah starts, & in reminder column will be how many days before i would like to get reminded.

Thank I'll have the script checking for כה כסלו & the year will be added in the script (as the year changes & i want to get reminded every year), & the script will calculate the Gregorian date based on the hebrew date that the script will feed it (date column & year), & then the script will check the criteria set in the third column if it should send a reminder email.
I downloaded a random Hebrew calendar app and it synced all the holidays to my calendar.
I'm not who you think I am.

Offline talmid chuchem

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2014
  • Posts: 934
  • Total likes: 30
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Google Sheets help
« Reply #4 on: January 30, 2017, 10:31:03 AM »
I downloaded a random Hebrew calendar app and it synced all the holidays to my calendar.
I don't need it only for holidays, I need it also for yurtzeitin.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9385
  • Total likes: 312
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Google Sheets help
« Reply #5 on: June 21, 2017, 12:56:15 PM »
Workflowy. You won't know what you're missing until you try it.

Offline Yammer

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2015
  • Posts: 3401
  • Total likes: 173
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Google Sheets help
« Reply #6 on: June 22, 2017, 04:06:44 AM »
Any way to encrypt a column in Google sheets?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9385
  • Total likes: 312
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Google Sheets help
« Reply #7 on: June 22, 2017, 07:54:43 AM »
Any way to encrypt a column in Google sheets?
not AFAIK, but I'll be happy if someone can correct me.
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9385
  • Total likes: 312
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Google Sheets help
« Reply #8 on: November 01, 2017, 08:01:49 AM »
Is there a way to protect a sheet from editing, but still allow people to hide/unhide columns?
Workflowy. You won't know what you're missing until you try it.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4428
  • Total likes: 205
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Google Sheets help
« Reply #9 on: November 01, 2017, 08:06:25 AM »
Is there a way to protect a sheet from editing, but still allow people to hide/unhide columns?
Hiding columns is considered editing, so Id venture to say no.
Once the game is over, the king and the pawn go back in the same box.

Offline ponash123

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2015
  • Posts: 235
  • Total likes: 22
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Google Sheets help
« Reply #10 on: February 22, 2018, 03:20:54 PM »
Anyone know if there's a way to do ctl ditto in Google Sheets?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9385
  • Total likes: 312
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Google Sheets help
« Reply #11 on: February 22, 2018, 03:26:28 PM »
Anyone know if there's a way to do ctl ditto in Google Sheets?
There's something similar: https://webapps.stackexchange.com/questions/74517/equivalent-of-ctrlapostrophe-in-google-sheets

Basically you need to select the cell with data to copy, and the cell below it where you want to paste, then press control-d
Workflowy. You won't know what you're missing until you try it.

Offline ponash123

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2015
  • Posts: 235
  • Total likes: 22
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Google Sheets help
« Reply #12 on: February 22, 2018, 03:27:42 PM »
There's something similar: https://webapps.stackexchange.com/questions/74517/equivalent-of-ctrlapostrophe-in-google-sheets

Basically you need to select the cell with data to copy, and the cell below it where you want to paste, then press control-d

Thanks!

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9385
  • Total likes: 312
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Google Sheets help
« Reply #13 on: November 05, 2018, 01:23:34 PM »
Are there any formula experts here?

I'm trying to append a total row to a QUERY formula with a pivot. It's complicated but the actual formula works B"H, I just need help with the total row. I'm using {} to append the row.

The total row has to do some subtraction, so I'm putting in there SUMIF based on the data that's somewhere else. The SUMIF works great if I manually type in the column letter for each column. Looking for a better way, I thought I could use the COLUMN() function. Problem: the COLUMN() function seems to refer to the column in which the function is placed, not the column where the result is displayed.

For testing purposes I put this in column I, and got this result:
Code: [Select]
={column(),column()}9    9

Anyone know of a way around this? I want the second 9 which is displayed in column J to show a 10.

Here's my entire function/formula in case you need it, but the question stands even without it:
Code: [Select]
={QUERY(Subsidies!A:F,"SELECT E, SUM(D) WHERE B <> ''  group by E pivot(F) ORDER BY E DESC");"TOTAL DUE",-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C))}
TIA!!
Workflowy. You won't know what you're missing until you try it.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4428
  • Total likes: 205
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Google Sheets help
« Reply #14 on: November 05, 2018, 01:57:34 PM »
Are there any formula experts here?

I'm trying to append a total row to a QUERY formula with a pivot. It's complicated but the actual formula works B"H, I just need help with the total row. I'm using {} to append the row.

The total row has to do some subtraction, so I'm putting in there SUMIF based on the data that's somewhere else. The SUMIF works great if I manually type in the column letter for each column. Looking for a better way, I thought I could use the COLUMN() function. Problem: the COLUMN() function seems to refer to the column in which the function is placed, not the column where the result is displayed.

For testing purposes I put this in column I, and got this result:
Code: [Select]
={column(),column()}9    9

Anyone know of a way around this? I want the second 9 which is displayed in column J to show a 10.

Here's my entire function/formula in case you need it, but the question stands even without it:
Code: [Select]
={QUERY(Subsidies!A:F,"SELECT E, SUM(D) WHERE B <> ''  group by E pivot(F) ORDER BY E DESC");"TOTAL DUE",-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C)),-(sumif(A:A,"Deposit",INDIRECT("C"&COLUMN()-7,false))-sumif(A:A,"Trip Subsidy",C:C))}
TIA!!
Looked through this? You're using the default () value instead of putting in the reference you need.

https://support.google.com/docs/answer/3093373?hl=en
Once the game is over, the king and the pawn go back in the same box.