I'm trying to create a to do list schedule like spreadsheet that would show only the data presented for the current week.

I've been having some difficulty figuring out how to display the data of columns for only this week and delete and add columns as days go by. The spreadsheet would display the current date in B1 (3/17/14) Current Day in B2 (Monday) and follow the next six days (to display 3/23/14 in H1 and Sunday in H2). This data would then shift tomorrow to display the new current date in B1 and end of week date in H1 (3/18/14 and 3/24/14) and the corresponding days in B2 and H2 (Tuesday and Monday). The entire columns of the previous B and current H would be deleted and added and everything else would shift over.

I haven't the slightest idea how dates work in Google Scripts and could use any help I can get.

  • Thanks for asking Jordan !! Please stick around for a few days (weeks), as the answer might take a while to be posted. Can you share a sample Google Spreadsheet with us? Mar 17, 2014 at 8:17
  • Here's a spreadsheet with dates shifting, B1 always containing Today and the other following. Did you want it to get data from someplace? What and where, in that case? Mar 17, 2014 at 9:24
  • That spreadsheet is quite similar to the one I have thusfar. I want to have the entire columns and the data in them shift in accordance with those dates. For example Today under 3/17/14 I would put some cell value "Exam" and under 3/18/14 I would have something like "Quiz" such that tomorrow the new today file which would be 3/18/14 would have "Quiz" under it and the old today file would have the cells in that column deleted as all the data would shift for the current week.
    – jordan
    Mar 17, 2014 at 20:38
  • I meant to write *cell not file
    – jordan
    Mar 17, 2014 at 20:47
  • The solution I'm looking for would delete the values of a range of cells and shift remaining data in that range to the left daily while leaving the cells to the right in that range blank.
    – jordan
    Mar 17, 2014 at 21:30

1 Answer 1


I would suggest something that might not quite meet "delete the values" etc but might serve as giving the appearance of doing so. Use two sheets, one (say RefData) with dates across say Row1 (say starting with March 17 in B1) and cell values such as Exam in say Row3 of the corresponding column. The intention being to have no need to ever delete the contents of this sheet - merely keep extending it to the right.

The other sheet would be very much like the example provided by @Punchlinern, though maybe with =text(B1,"dddd") in B2 (copied across) since WEEKDAY is not necessary, and in say B4 and copied across:


This sheet would provide a view of RefData and be populated exclusively by formulae - all data would be entered in RefData.

The optional fourth parameter for offset might be applied if to show more than just a single cell value per day, perhaps a row for each of morning, lunch, afternoon, dinner, evening.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.