Search code examples
javascriptgoogle-sheetsgoogle-sheets-api

Is there a find and replace method in Google Spreadsheet api?

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.

Solution

  • 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 of replaced 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 from const SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'; to const 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.

    References: