Is there a find and replace method in Google Spreadsheet api? there somewhere I found find and replace request but unable to determine what URL should I use for that. Google's documentation isn't much clear about that.
CodePudding user response:
About find and replace request
, I guessed that you might be thinking of FindReplaceRequest, you can use this with "Method: spreadsheets.batchUpdate" of Sheets API.
The sample request is as follows.
Endpoint and request body
The content type of the request header is application/json
.
POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate
{
"requests": [
{
"findReplace": {
"sheetId": 0,
"find": "sample",
"replacement": "replaced",
"matchEntireCell": true
}
}
]
}
- In this request body, a value of
sample
is searched and it is replaced with a value ofreplaced
in the specification sheet in Google Spreadsheet. - If you want to search and replace in all sheets, please modify
"sheetId": 0
to"allSheets": true
. - Regex can be also used.
Sample curl command:
The sample curl command is as follows. I thought that the sample curl command might help to think of the script you are using.
$ curl --request POST \
'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]:batchUpdate' \
--header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--data '{"requests":[{"findReplace":{"allSheets":false,"sheetId":0,"find":"","replacement":"","matchEntireCell":false}}]}' \
--compressed
Sample javascript:
When I saw your tag, I noticed javascript
. So, I added Javascript as a sample script. In this sample, the sheet ID of 0
is used. Please modify this for your actual situation.
const spreadsheetId = "###"; // Please set your Spreadsheet ID.
gapi.client.sheets.spreadsheets.batchUpdate({
spreadsheetId,
resource: {
requests: [
{
findReplace: {
sheetId: 0,
find: "sample",
replacement: "replaced",
matchEntireCell: true
}
}
]
}
}).then(({result}) => {
console.log(result)
}, function(reason) {
console.error(reason.result.error.message);
});
- If you want to see the whole script of Javascript, you can check Quickstart for Javascript. In this case, I think that you can use this script at
// Fetch first 10 files
. But, before you use this, please modify the scope fromconst SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly';
toconst SCOPES = 'https://www.googleapis.com/auth/spreadsheets';
.
Note:
- I'm not sure whether I could correctly understand
javscript
tag in your tag. If you want to achieve your goal using Google Apps Script. I think that TextFinder of Spreadsheet service (SpreadsheetApp) instead of Sheets API might be suitable.