Does anyone know if there is a way to use the Google Sheets API to get the email address of a people chip? It seems like the only values it will give me are the person's full name as displayed in the cell.
While trying to figure this out I discovered that the name turns into an email address if you use "Format > Clear Formatting". I hoped I could hack this by copying the value into a hidden sheet, clearing the formatting, and then get the email address there. But though Google Apps Script has a function for executing that on a cell, I have found no way to do it with the Sheets API. I tried clearing all the possible formats I could via batchUpdate with an updateCellsRequest but that isn't turning the people chip into an email address.
I'm at the point where if I really want to automate this, I will need to use the scripts API as well, to deploy and execute a tiny Apps Script just to run clearFormat on a cell. I'd like to avoid this much complication, especially since Apps Script doesn't work for service accounts.
As Tanaike mentioned this is not possible at the moment but there is already a feature request in the Google Issue tracker related to adding the people chips to the Sheets API. You can check the request here.
I would suggest posting a comment on it and explain why you are interested in this feature. Or if you think your request has a different approach you can also submit a new feature request for the Sheets API here.