Created
January 23, 2022 07:44
-
-
Save wendeehsu/6929e7d1e283eb6e9f0eb4f9545927b4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function batchUpdate() { | |
// https://docs.google.com/spreadsheets/d/{this_is_Sheet_id}/edit#gid=0 | |
let originalSheetId = "18vPr_Kghkv16gFp0KBptzY3yu8J4fp6b9NobppeHMWY"; | |
let backupSheetId = "1SSHx4RiytJoV3X6EgtSb0bNow1YIt8txfrllEIZF9Pc"; | |
let sheet = SpreadsheetApp.openById(originalSheetId).getSheetByName("transaction"); | |
let rowNum = sheet.getLastRow(); | |
// get data in certain range from original sheet | |
let response = Sheets.Spreadsheets.Values.batchGet( | |
originalSheetId, {ranges: ['transaction!A1:N' + rowNum]}); | |
let values = response.valueRanges[0].values; | |
// If your data range is not continuous, your batchGet range will be: | |
// ranges: ['transaction!A1:C' + rowNum, 'transaction!F1:N' + rowNum, ...] | |
// response.valueRanges[n].values is the corresponding values of each range | |
// specify data range and new values we want to set in Backup Sheet | |
let request = { | |
'valueInputOption': 'USER_ENTERED', | |
'data': [ | |
{ | |
'range': 'Sheet3!A1:N' + values.length, | |
'majorDimension': 'ROWS', | |
'values': values | |
} | |
] | |
}; | |
// Again, if the range to update is not continuous, `data` in the request above will be: | |
// 'data': [{'range': 'Sheet3!A1:C' + values1.length, 'majorDimension': 'ROWS', 'values': values1}, | |
// {'range': 'Sheet3!F1:N' + values2.length, 'majorDimension': 'ROWS', 'values': values2}, ...] | |
SpreadsheetApp.openById(backupSheetId).getSheetByName("Sheet3").getDataRange().clearContent(); | |
// flush() will force gsheet to apply all pending changes. | |
// If not added, sometimes update values will run before clearContent :( | |
SpreadsheetApp.flush(); | |
Sheets.Spreadsheets.Values.batchUpdate(request, backupSheetId); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment