Home > Software engineering >  Error in Google webapp using URLFetchApp: range exceeds grid limits when inserting new row
Error in Google webapp using URLFetchApp: range exceeds grid limits when inserting new row

Time:03-18

With great enter image description here

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:

enter image description here

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.

  • Related