Update spreadsheets

/**
 * 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.
letupdateSpreadsheetPropertiesRequest=
Sheets.newUpdateSpreadsheetPropertiesRequest();
updateSpreadsheetPropertiesRequest.properties=
Sheets.newSpreadsheetProperties();
updateSpreadsheetPropertiesRequest.properties.title=title;
updateSpreadsheetPropertiesRequest.fields='title';
// Find and replace text.
letfindReplaceRequest=Sheets.newFindReplaceRequest();
findReplaceRequest.find=find;
findReplaceRequest.replacement=replacement;
findReplaceRequest.allSheets=true;
letrequests=[Sheets.newRequest(),Sheets.newRequest()];
requests[0].updateSpreadsheetProperties=
updateSpreadsheetPropertiesRequest;
requests[1].findReplace=findReplaceRequest;
letbatchUpdateRequest=Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateRequest.requests=requests;
// Add additional requests (operations)
constresult=
Sheets.Spreadsheets.batchUpdate(batchUpdateRequest,spreadsheetId);
returnresult;
}catch(err){
// TODO (developer) - Handle exception
console.log('Failed with error %s',err.message);
}
};

importcom.google.api.client.googleapis.json.GoogleJsonError;
importcom.google.api.client.googleapis.json.GoogleJsonResponseException;
importcom.google.api.client.http.HttpRequestInitializer;
importcom.google.api.client.http.javanet.NetHttpTransport;
importcom.google.api.client.json.gson.GsonFactory;
importcom.google.api.services.sheets.v4.Sheets;
importcom.google.api.services.sheets.v4.SheetsScopes;
importcom.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
importcom.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
importcom.google.api.services.sheets.v4.model.FindReplaceRequest;
importcom.google.api.services.sheets.v4.model.FindReplaceResponse;
importcom.google.api.services.sheets.v4.model.Request;
importcom.google.api.services.sheets.v4.model.SpreadsheetProperties;
importcom.google.api.services.sheets.v4.model.UpdateSpreadsheetPropertiesRequest;
importcom.google.auth.http.HttpCredentialsAdapter;
importcom.google.auth.oauth2.GoogleCredentials;
importjava.io.IOException;
importjava.util.ArrayList;
importjava.util.Collections;
importjava.util.List;
/* Class to demonstrate the use of Spreadsheet Batch Update API */
publicclass 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.
 */
publicstaticBatchUpdateSpreadsheetResponsebatchUpdate(StringspreadsheetId,
Stringtitle,
Stringfind,
Stringreplacement)
throwsIOException{
/* Load pre-authorized user credentials from the environment.
 TODO(developer) - See https://developers.google.com/identity for
 guides on implementing OAuth2 for your application. */
GoogleCredentialscredentials=GoogleCredentials.getApplicationDefault()
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
HttpRequestInitializerrequestInitializer=newHttpCredentialsAdapter(
credentials);
// Create the sheets API client
Sheetsservice=newSheets.Builder(newNetHttpTransport(),
GsonFactory.getDefaultInstance(),
requestInitializer)
.setApplicationName("Sheets samples")
.build();
List<Request>requests=newArrayList<>();
BatchUpdateSpreadsheetResponseresponse=null;
try{
// Change the spreadsheet's title.
requests.add(newRequest()
.setUpdateSpreadsheetProperties(newUpdateSpreadsheetPropertiesRequest()
.setProperties(newSpreadsheetProperties()
.setTitle(title))
.setFields("title")));
// Find and replace text.
requests.add(newRequest()
.setFindReplace(newFindReplaceRequest()
.setFind(find)
.setReplacement(replacement)
.setAllSheets(true)));
BatchUpdateSpreadsheetRequestbody=
newBatchUpdateSpreadsheetRequest().setRequests(requests);
response=service.spreadsheets().batchUpdate(spreadsheetId,body).execute();
FindReplaceResponsefindReplaceResponse=response.getReplies().get(1).getFindReplace();
System.out.printf("%d replacements made.",findReplaceResponse.getOccurrencesChanged());
}catch(GoogleJsonResponseExceptione){
// TODO(developer) - handle error appropriately
GoogleJsonErrorerror=e.getDetails();
if(error.getCode()==404){
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
}else{
throwe;
}
}
returnresponse;
}
}

functionbatchUpdate(spreadsheetId,title,find,replacement,callback){
constrequests=[];
// 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) ...
constbatchUpdateRequest={requests:requests};
gapi.client.sheets.spreadsheets.batchUpdate({
spreadsheetId:spreadsheetId,
resource:batchUpdateRequest,
}).then((response)=>{
constfindReplaceResponse=response.result.replies[1].findReplace;
console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);
if(callback)callback(response);
});
}catch(err){
document.getElementById('content').innerText=err.message;
return;
}
}

import{GoogleAuth}from'google-auth-library';
import{google}from'googleapis';
/**
 * Performs a batch update on a spreadsheet.
 * Updates the spreadsheet title and finds and replaces a string.
 * @param {string} spreadsheetId The ID of the spreadsheet to update.
 * @param {string} title The new title for the spreadsheet.
 * @param {string} find The string to find.
 * @param {string} replacement The string to replace the found string with.
 * @return {Promise<object>} The response from the batch update.
 */
asyncfunctionbatchUpdate(spreadsheetId,title,find,replacement){
// Authenticate with Google and get an authorized client.
constauth=newGoogleAuth({
scopes:'https://www.googleapis.com/auth/spreadsheets',
});
// Create a new Sheets API client.
constservice=google.sheets({version:'v4',auth});
// Create a list of requests to be executed in the batch update.
constrequests=[];
// Request to change the spreadsheet's title.
requests.push({
updateSpreadsheetProperties:{
properties:{
title,
},
fields:'title',
},
});
// Request to find and replace text.
requests.push({
findReplace:{
find,
replacement,
allSheets:true,
},
});
// Add more requests here if needed.
// Create the batch update request.
constbatchUpdateRequest={requests};
// Execute the batch update request.
constresponse=awaitservice.spreadsheets.batchUpdate({
spreadsheetId,
requestBody:batchUpdateRequest,
});
// Get the response from the find and replace request and log the number of occurrences.
constfindReplaceResponse=response.data.replies[1].findReplace;
console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);
returnresponse;
}

<?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();
 }
 }

importgoogle.auth
fromgoogleapiclient.discoveryimport build
fromgoogleapiclient.errorsimport HttpError
defsheets_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."