Add-GSSheetValues and Export-GSSheet: Leading single quotation mark is added to dates?

[jeffreymcclain]

Describe the bug When using "Add-GSSheetValues" to add a new row at the bottom of the sheet, dates have a leading single quotation mark appended to the front of them.

To Reproduce Steps to reproduce the behavior:

1. $items = "7/2/2020"
2. Add-GSSheetValues -SpreadsheetId $SpreadsheetId -Array $items -Range 'A:Z' -Append
3. Export-GSSheet -SpreadsheetId $SpreadsheetId -Array $items -Range 'A:Z' -Append

Expected behavior A new row should be added to the bottom of the sheet with the value 7/2/2020. Instead, the value '7/2/2020 is added instead.

Screenshots https://i.imgur.com/OWGY3kn.png

Environment (please complete the following information):

• OS: Windows 10 64-bit, v2004
• PowerShell Version: PowerShell 7.0.2
• PSGSuite Version: 2.36.4

[FISHMANPET]

The single quote indicates to Google Sheets that it shouldn't try and process the value of the cell and just display it RAW.

The API defaults to ValueInputOption RAW for changes, which results in that single quote going there. If you specify user entered as -ValueInputOption USER_ENTERED with Export-GSSheet or -ValueInputOption USERENTERED with Add-GSSheetValues it'll behave as if a user was typing those values into the web interface. And blame the inconsistency on those parameter names on the underlying Google API and not this module...

[jeffreymcclain]

@FISHMANPET Thanks, I just tested the parameter you mentioned and it seems to work!

As a follow up question, do you know if there is a way to have the PSGSuite module append rows that duplicate the formatting that is applied to the column?

For example, I have formatted the Date column to automatically display Tuesday, August 5 for the value 8/5/2020. I have applied this formula to the entire column, so when I manually insert a new row at the bottom and enter a date, the displayed value changes automatically.

However, when using the module to append a row with the value "7/2/2020" for example, the formatting change does not seem to occur (even without the leading single quote, which I had initially assumed was the culprit).

Apologies if this doesn't directly relate to this module, one of my objectives is to automate a "rolling spreadsheet" used for reservations (where old rows are removed from the top of the sheet as old dates pass and new rows are added to to the bottom of the sheet representing future dates).

I'm aware there are better ways of handling reservations such as using a dedicated website, but I'd like to avoid the work and fees associated with web development, hosting, and maintenance if at all possible. The ad-hoc Spreadsheet schedule has been working fine for my purposes, but ideally I'd like to avoid having to login manually a few times a month to delete and append rows.

Edit: I just did some more testing and realized that if I simply pass "Thursday, July 2" as the value directly using the PSGSuite command then it works fine, so I'll just do the date formatting on the PowerShell side I guess.

Thanks again for helping me troubleshoot the original issue!

[FISHMANPET]

The API supports formatting but this module currently doesn't, such things are on my to-do list though.

The way I dealt with this is I had a project that added a new line to a spreadsheet every day, so I just applied my desired formatting to all 1000 rows of the spreadsheet, which means it won't be a problem for about 3 years...

If you're rotating by deleting the row in the web interface (I don't think PSGsuite can remove a row yet) then you'd have to keep replying formatting to new rows manually occasionally. You could remove old rows completely with the module by reading all the data, removing the rows in your PowerShell object, then writing that back to the Sheet, which would keep the formatting on the existing rows intact.

Otherwise you'll have to wait for me or Nate to add formatting to this module to apply that formatting to the row when you write the data.

Fake edit: Or there you go, that works too!

[jeffreymcclain]

Thanks for the alternate ideas! I had originally considered just adding a bunch of rows and formatting them preemptively as a workaround, and I might still wind up going with that approach if I have issues with the current implementation or based on user feedback.

I also appreciate your explanation of how to remove the old rows as well, admittedly I haven't actually written that code yet but I planned to do something similar based on code I've written for modifying .csv and Excel files (import the file contents into powershell, make changes / deletions, and lastly export the entire object with the same file name / location to overwrite the original).

Cheers!