在上一步的巨大
代碼：
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
}
}
執行代碼后：
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.
uj5u.com熱心網友回復：
嘗試這個：
function postData(ssID, updateRange, data) {
const ss = SpreadsheetApp.openById(ssID);
const sh = ss.getRange(updateRange).getSheet();//I assumed range is in A1 notation
const lr = sh.getLastRow();
if (lr == sh.getMaxRows()) {
sh.insertRowsAfter(lr, data.length);
}
if (clientToken) {
var url = `https://sheets.googleapis.com/v4/spreadsheets/${ssID}/values/${updateRange}?valueInputOption=RAW`
var options = {
muteHttpExceptions: true,
contentType: 'application/json',
method: 'put',
payload: JSON.stringify(data),
headers: { Authorization: 'Bearer ' clientToken }
};
var response = UrlFetchApp.fetch(url, options);
var responseCode = response.getResponseCode();
var responseBody = response.getContentText();
if (responseCode === 200) {
var responseJson = JSON.parse(responseBody)
return responseJson;
} else {
Logger.log(Utilities.formatString("Request failed. Expected 200, got %d: %s", responseCode, responseBody))
return responseCode;
}
} else {//handle failed authorization }
} //end postData
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/446571.html