Issue and workaround:
When I saw CellFormat of the official document, I thought that the value of textRotation can be retrieved by the fields of sheets(data(rowData(values(userEnteredFormat(textRotation))))) when the method of "spreadsheets.get" is used. But when I tested this, no values are returned. So I thought that this might be a bug. And, when I searched this at the Google issue tracker, I found https://issuetracker.google.com/issues/146274218. From this situation, in the current stage, it seems that the values of textRotation cannot be retrieved by the method of "spreadsheets.get" of Sheets API.
When you want to retrieve the value of textRotation using a script, as a current workaround, you can achieve it using the Web Apps created by Google Apps Script as a wrapper. In this answer, I would like to propose the workaround.
When this workaround is reflected to the flow for your situation, it becomes as follows.
Usage:
1. Create new project of Google Apps Script.
Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.
If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.
2. Prepare Web Apps side. (server side)
Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps. This Web Apps is used as an API.
Server side: Google Apps Script
function doGet(e) {
const key = "sampleKey";
const id = e.parameter.spreadsheetId;
const sheetName = e.parameter.sheetName;
if (e.parameter.key != key || !id || !sheetName) {
return ContentService.createTextOutput(JSON.stringify({message: "Error."})).setMimeType(ContentService.MimeType.JSON);
}
const sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
const textRotations = sheet.getDataRange().getTextRotations().map(r => r.map(c => ({angle: c.getDegrees(), vertical: c.isVertical()})));
return ContentService.createTextOutput(JSON.stringify(textRotations)).setMimeType(ContentService.MimeType.JSON);
}
3. Deploy Web Apps.
- On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
- Select "Me" for "Execute the app as:".
- By this, the script is run as the owner.
- Select "Anyone, even anonymous" for "Who has access to the app:".
- Of course, you can use the access token for this situation. But in this case, as a simple setting, I use the access key instead of the access token.
- Click "Deploy" button as new "Project version".
- Automatically open a dialog box of "Authorization required".
- Click "Review Permissions".
- Select own account.
- Click "Advanced" at "This app isn't verified".
- Click "Go to ### project name ###(unsafe)"
- Click "Allow" button.
- Click "OK".
- Copy the URL of Web Apps. It's like
https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.
3. Testing.
As a simple test, when it requests to the Web Apps using a curl command, it becomes as follows. Please set the spreadsheet ID and sheet name. When the Web Apps is correctly deployed, the values are returned.
$ curl -L "https://script.google.com/macros/s/###/exec?spreadsheetId=###&sheetName=Sheet1&key=sampleKey"
Result:
The data range is used for retrieving the values. So for example, when the values are set to the cells "A1:C3" in "Sheet1", the following result is returned.
[
[{"angle":30,"vertical":false},{"angle":0,"vertical":true},{"angle":0,"vertical":false}],
[{"angle":30,"vertical":false},{"angle":30,"vertical":false},{"angle":0,"vertical":false}]
]
- In this case, the cells "A1, A2, B2" have the text rotation with 30 degree. And, the cell "B1" has the vertical direction.
Note:
- When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this.
References:
.