Обновить таблицы

/**  * Updates the specified sheet using advanced sheet services  * @param {string} spreadsheetId id of the spreadsheet to be updated  * @param {string} title name of the sheet in the spreadsheet to be updated  * @param {string} find string to be replaced  * @param {string} replacement the string to replace the old data  * @returns {*} the updated spreadsheet  */ Snippets.prototype.batchUpdate = (spreadsheetId, title,   find, replacement) => {   // This code uses the Sheets Advanced Service, but for most use cases   // the built-in method SpreadsheetApp.getActiveSpreadsheet()   //     .getRange(range).setValues(values) is more appropriate.    try {     // Change the spreadsheet's title.     let updateSpreadsheetPropertiesRequest =       Sheets.newUpdateSpreadsheetPropertiesRequest();     updateSpreadsheetPropertiesRequest.properties =       Sheets.newSpreadsheetProperties();     updateSpreadsheetPropertiesRequest.properties.title = title;     updateSpreadsheetPropertiesRequest.fields = 'title';      // Find and replace text.     let findReplaceRequest = Sheets.newFindReplaceRequest();     findReplaceRequest.find = find;     findReplaceRequest.replacement = replacement;     findReplaceRequest.allSheets = true;      let requests = [Sheets.newRequest(), Sheets.newRequest()];     requests[0].updateSpreadsheetProperties =       updateSpreadsheetPropertiesRequest;     requests[1].findReplace = findReplaceRequest;      let batchUpdateRequest = Sheets.newBatchUpdateSpreadsheetRequest();     batchUpdateRequest.requests = requests;      // Add additional requests (operations)     const result =       Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, spreadsheetId);     return result;   } catch (err) {     // TODO (developer) - Handle exception     console.log('Failed with error %s', err.message);   } };

import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest; import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse; import com.google.api.services.sheets.v4.model.FindReplaceRequest; import com.google.api.services.sheets.v4.model.FindReplaceResponse; import com.google.api.services.sheets.v4.model.Request; import com.google.api.services.sheets.v4.model.SpreadsheetProperties; import com.google.api.services.sheets.v4.model.UpdateSpreadsheetPropertiesRequest; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.ArrayList; import java.util.Collections; import java.util.List;  /* Class to demonstrate the use of Spreadsheet Batch Update API */ public class BatchUpdate {   /**    * Updates spreadsheet's title and cell values.    *    * @param spreadsheetId - Id of the spreadsheet.    * @param title         - New title of the spreadsheet.    * @param find          - Find cell values    * @param replacement   - Replaced cell values    * @return response metadata    * @throws IOException - if credentials file not found.    */   public static BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId,                                                            String title,                                                            String find,                                                            String replacement)       throws IOException {         /* Load pre-authorized user credentials from the environment.            TODO(developer) - See https://developers.google.com/identity for             guides on implementing OAuth2 for your application. */     GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()         .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));     HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(         credentials);      // Create the sheets API client     Sheets service = new Sheets.Builder(new NetHttpTransport(),         GsonFactory.getDefaultInstance(),         requestInitializer)         .setApplicationName("Sheets samples")         .build();      List<Request> requests = new ArrayList<>();     BatchUpdateSpreadsheetResponse response = null;     try {       // Change the spreadsheet's title.       requests.add(new Request()           .setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest()               .setProperties(new SpreadsheetProperties()                   .setTitle(title))               .setFields("title")));       // Find and replace text.       requests.add(new Request()           .setFindReplace(new FindReplaceRequest()               .setFind(find)               .setReplacement(replacement)               .setAllSheets(true)));        BatchUpdateSpreadsheetRequest body =           new BatchUpdateSpreadsheetRequest().setRequests(requests);       response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();       FindReplaceResponse findReplaceResponse = response.getReplies().get(1).getFindReplace();        System.out.printf("%d replacements made.", findReplaceResponse.getOccurrencesChanged());     } catch (GoogleJsonResponseException e) {       // TODO(developer) - handle error appropriately       GoogleJsonError error = e.getDetails();       if (error.getCode() == 404) {         System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);       } else {         throw e;       }     }     return response;   } }

function batchUpdate(spreadsheetId, title, find, replacement, callback) {   const requests = [];   // Change the spreadsheet's title.   requests.push({     updateSpreadsheetProperties: {       properties: {         title: title,       },       fields: 'title',     },   });   // Find and replace text.   requests.push({     findReplace: {       find: find,       replacement: replacement,       allSheets: true,     },   });   try {     // Add additional requests (operations) ...     const batchUpdateRequest = {requests: requests};     gapi.client.sheets.spreadsheets.batchUpdate({       spreadsheetId: spreadsheetId,       resource: batchUpdateRequest,     }).then((response) => {       const findReplaceResponse = response.result.replies[1].findReplace;       console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);       if (callback) callback(response);     });   } catch (err) {     document.getElementById('content').innerText = err.message;     return;   } }

/**  * Updates the Spreadsheet title. Finds and replaces a string in the sheets.  * @param {string} spreadsheetId The Spreadsheet to update  * @param {string} title The new Spreadsheet title  * @param {string} find The text to find  * @param {string} replacement The text to replace  * @return {obj} holding the information regarding the replacement of strings  */ async function batchUpdate(spreadsheetId, title, find, replacement) {   const {GoogleAuth} = require('google-auth-library');   const {google} = require('googleapis');    const auth = new GoogleAuth({     scopes: 'https://www.googleapis.com/auth/spreadsheets',   });    const service = google.sheets({version: 'v4', auth});   const requests = [];   // Change the spreadsheet's title.   requests.push({     updateSpreadsheetProperties: {       properties: {         title,       },       fields: 'title',     },   });   // Find and replace text.   requests.push({     findReplace: {       find,       replacement,       allSheets: true,     },   });   // Add additional requests (operations) ...   const batchUpdateRequest = {requests};   try {     const response = await service.spreadsheets.batchUpdate({       spreadsheetId,       resource: batchUpdateRequest,     });     const findReplaceResponse = response.data.replies[1].findReplace;     console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);     return response;   } catch (err) {     // TODO (developer) - Handle exception     throw err;   } }

<?php use Google\Client; use Google\Service\Drive; use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;  /**  * to batch update a spreadsheet  */ function batchUpdate($spreadsheetId, $title, $find, $replacement)     {            /* Load pre-authorized user credentials from the environment.            TODO(developer) - See https://developers.google.com/identity for             guides on implementing OAuth2 for your application. */         $client = new Google\Client();         $client->useApplicationDefaultCredentials();         $client->addScope(Google\Service\Drive::DRIVE);         $service = new Google_Service_Sheets($client);         try{             //execute the request             $requests = [                 new Google_Service_Sheets_Request([               'updateSpreadsheetProperties' => [                   'properties' => [                       'title' => $title                     ],                     'fields' => 'title'               ]           ]),           new Google_Service_Sheets_Request([               'findReplace' => [                   'find' => $find,                   'replacement' => $replacement,                   'allSheets' => true                   ]                   ])                 ];                 $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([                     'requests' => $requests                 ]);         $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);         $findReplaceResponse = $response->getReplies()[1]->getFindReplace();         printf("%s replacements made.\n",         $findReplaceResponse->getOccurrencesChanged());         return $response;     }     catch(Exception $e) {         // TODO(developer) - handle error appropriately         echo 'Message: ' .$e->getMessage();       }     }

import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError   def sheets_batch_update(spreadsheet_id, title, find, replacement):   """   Update the sheet details in batch, the user has access to.   Load pre-authorized user credentials from the environment.   TODO(developer) - See https://developers.google.com/identity   for guides on implementing OAuth2 for the application.   """    creds, _ = google.auth.default()   # pylint: disable=maybe-no-member    try:     service = build("sheets", "v4", credentials=creds)      requests = []     # Change the spreadsheet's title.     requests.append(         {             "updateSpreadsheetProperties": {                 "properties": {"title": title},                 "fields": "title",             }         }     )     # Find and replace text     requests.append(         {             "findReplace": {                 "find": find,                 "replacement": replacement,                 "allSheets": True,             }         }     )     # Add additional requests (operations) ...      body = {"requests": requests}     response = (         service.spreadsheets()         .batchUpdate(spreadsheetId=spreadsheet_id, body=body)         .execute()     )     find_replace_response = response.get("replies")[1].get("findReplace")     print(         f"{find_replace_response.get('occurrencesChanged')} replacements made."     )     return response    except HttpError as error:     print(f"An error occurred: {error}")     return error   if __name__ == "__main__":   sheets_batch_update("spreadsheet_id", "title", "find", "replacement")

requests = [] # Change the name of sheet ID '0' (the default first sheet on every # spreadsheet) requests.push({                 update_sheet_properties: {                   properties: { sheet_id: 0, title: 'New Sheet Name' },                   fields:     'title'                 }               }) # Find and replace text requests.push({                 find_replace: {                   find:        find,                   replacement: replacement,                   all_sheets:  true                 }               }) # Add additional requests (operations) ...  body = { requests: requests } result = service.batch_update_spreadsheet(spreadsheet_id, body, {}) find_replace_response = result.replies[1].find_replace puts "#{find_replace_response.occurrences_changed} replacements made."