Code:
function request(ssID="spreadsheet id here",updateRange = "A1499:A1500",data) {
if (clientToken) {
var ssGetUrl= `https://sheets.googleapis.com/v4/spreadsheets/${ssID}`
var options = {
muteHttpExceptions: true,
contentType: 'application/json',
method:'get',
headers: { Authorization: 'Bearer ' clientToken }
};
var ssGetresponse= JSON.parse(UrlFetchApp.fetch(ssGetUrl,options));
var sheets = ssGetresponse.sheets;
var rowCount = 0;
var sheetId = 0;
sheets.forEach(sheet => {
if(sheet.properties.sheetId == sheetId){
rowCount = sheet.properties.gridProperties.rowCount
}
})
var num = parseInt(updateRange.split(":")[1].replace(/[^0-9]/g,'')); //remove letters in updateRange and convert it to string
if(rowCount < num){
var diff = num - rowCount;
var resource = {
"requests": [
{
"appendDimension": {
"length": diff,
"dimension": "ROWS",
"sheetId": 0
}
}
]
};
var ssBatchUpdateUrl= `https://sheets.googleapis.com/v4/spreadsheets/${ssID}:batchUpdate`
var options = {
muteHttpExceptions: true,
contentType: 'application/json',
method:'post',
payload: JSON.stringify(resource),
headers: { Authorization: 'Bearer ' clientToken }
};
var response= JSON.parse(UrlFetchApp.fetch(ssBatchUpdateUrl,options));
}
//insert code for updating range values
}
}
After executing code:
Note: The demo above is for increasing the number of rows when the update range is beyond the sheet's actual rows. For instance, if you have a range that the column is beyond the sheet's actual column, you can update the script to also read the columnCount, modify the parser to also get column part of A1 Notation and add another entry on appendDimension
with COLUMN as dimension.