I’m working on a Google Apps Script project where I’m integrating with the Notion API to fetch data from a Notion database. The script works well in fetching all the columns of the database, but I’m encountering an issue where it only retrieves the last 100 entries, whereas I need to fetch more data (all entries) from the database.

Here’s my script:

function fetchNotionData() {
  var pageId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; 
  var integrationToken = 'Bearer secret_yyyyyyyyyyyyyyyyyyyyyyyyyy'; 

  // Define the Notion API URL
  var notionUrl = 'https://api.notion.com/v1/databases/' + pageId + '/query';
  
  // Set the headers for the request
  var headers = {
    'Authorization': integrationToken,
    'Notion-Version': '2022-06-28'
  };

// Set the options for the request
  var options = {
    'method': 'post', // Use POST method for querying
    'headers': headers,
    'muteHttpExceptions': true // Add option to examine full response
  };

  // Fetch data from Notion API
  var response = UrlFetchApp.fetch(notionUrl, options);
  var content = response.getContentText();
  var responseData = JSON.parse(content);

  // Handle data retrieval
  if (responseData && responseData.results) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.clear(); // Clear existing data

    var header = Object.keys(responseData.results[0].properties);
    var resultHeader = [header];

    sheet.getRange(1, 1, 1, header.length).setValues(resultHeader);

    responseData.results.forEach(function(entry) {
      var entryRow = [];
      header.forEach(function(prop) {
        var propValue = '';
        if (entry.properties[prop]) {
          propValue = handlePropertyType(entry.properties[prop]);
        }
        entryRow.push(propValue);
      });
      sheet.appendRow(entryRow);
    });
  } else {
    Logger.log('No data retrieved from Notion API');
  }
}

function handlePropertyType(property) {
  var propType = property.type;
  switch(propType) {
    case 'title':
      return property.title[0].plain_text;
    case 'text':
      return property.text.content;
    case 'number':
      return property.number;
    case 'select':
      return property.select.name;
    case 'date':
      return property.date.start;
    case 'formula':
      // Check the content type of the formula
      if (property.formula.type === 'string') {
        return property.formula.string;
      } else if (property.formula.type === 'number') {
        return property.formula.number;
      } else {
        return ''; // Handle other types as needed
      }
    case 'status':
      return property.status.name;
    case 'relation':
      return property.relation.string;
    case 'rollup':
      return property.rollup.string;
    default:
      return handleUnsupportedType(property);
  }
}

function handleUnsupportedType(property) {
  if (property.rich_text && property.rich_text[0] && property.rich_text[0].text) {
    return property.rich_text[0].text.content; // Handle unsupported text type
  } else if (property.number) {
    return property.number; // Handle unsupported number type
  } else {
    return property.checkbox; // Handle unsupported checkbox type
  }
}