Can anyone tell me why my scrape stops at 101 rows?

thecondafe

Hello,

I'm using the script described below to import data from a smartsheet report into a google sheet tab. When using the sheets portion from the URL G-sheet will comfortably import all rows. However, when I change to reports, the script will continue to stop at 101 rows. I've tried sorting rows, minimizing row count.

I used this in google's app script editor:   

var smartSheetToken = "xxxxx";

var smartSheetID = "xxxxxx";

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SS Import");

    var url = "https://api.smartsheet.com/2.0/reports/" + smartSheetID;

    var response = UrlFetchApp.fetch(

        url, { headers: {Authorization: 'Bearer ' + smartSheetToken}}

    );

    var result = JSON.parse(response.getContentText());

    sheet.clear();

    var tab = [];

    for (var column in result.columns){

      var value = result.columns[column]["title"];

      tab.push(value);

    }

    sheet.appendRow(tab);

    for(var row in result.rows){

        var cells = result.rows[row]["cells"];

        var tab = [];

        for(var cell in cells){

            var value = cells[cell]["value"] == undefined ? "" : cells[cell]["value"];

            tab.push(value);

        }

        sheet.appendRow(tab);

    }  

}

Ryan Kramer

It's likely because of this parameter -

Pagesize - The maximum number of items to return per page. Unless otherwise stated for a specific endpoint, defaults to 100. If neither pageSize nor page is specified, returns all rows in the sheet. If only page is specified, defaults to a page size of 100.

https://smartsheet.redoc.ly/tag/reports#operation/getReport

Ryan

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

thecondafe

Thank you for the response...

I'm completely new to scripting...can you elaborate on where I should specify the page size within the script?

Ryan Kramer

I don't remember how to do it using their python SDK.

However, you can use the requests framework -

url = "https://api.smartsheet.com/2.0/reports/{}".format(report_id)

# Set the query parameters
params = {
    "pageSize": 1000
}

# Make the API request
response = requests.get(url, headers={"Authorization": "Bearer {}".format(cfg["smartsheet"]['api_token'])}, params=params)
json.loads(response.content.decode("utf-8"))['rows']

Ryan

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!