How to monitor page index status using the Google Search Console API and Apps Script in a Google Sheet. For instructions on how to configure this script please see https://ithoughthecamewithyou.com/post/monitor-page-index-status-with-google-sheets-apps-script-and-the-google-search-console-api

Code.gs

/*Steps to Run
1-) Open appscripts and than go to project settings, enable show "appsscript.json" manifest file in editor
2-)Go to Editor and open appsscript.json replace it with following 

{
  "timeZone": "America/New_York",
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/webmasters", "https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/script.send_mail"],
  "runtimeVersion": "V8"
}

3-) Go to settings again and enter your Project Number (that you are using for search console API calls) which you can find it on Google Cloud Platform Dashboard.

4-) Fill
var AlertEmail = '';
var SitemapUrl = '';
var SearchConsoleProperty = '';

5-) Thats all ( ͡❛ ͜ʖ ͡❛)✊ run it via sheet ui (Update Data)
*/


var AlertEmail = '';
var SitemapUrl = '';
var SearchConsoleProperty = '';
var token = ScriptApp.getOAuthToken();

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Search Console')
    .addItem('Update Data', 'runUpdate')
    .addToUi();
}

function runUpdate() {
  var pages = new Map();

  Logger.log('Loading known pages...');
  readPages(pages);

  Logger.log('Adding any new pages from sitemap...');
  updatePagesFromSitemap(pages);

  Logger.log('Updating page data from search console...');
  updatePagesFromGsc(pages);

  Logger.log('Writing pages to sheet...');
  writePages(pages);
}

function updatePagesFromGsc(pages) {
  var changes = [];
  var day = new Date().getDay();

  for (const [key, value] of pages) {
    try {
      if (value.checkDay != day) {
        continue;
      }

      var request = {
        "inspectionUrl": value.url,
        "siteUrl": SearchConsoleProperty
      };

      var response = UrlFetchApp.fetch('https://searchconsole.googleapis.com/v1/urlInspection/index:inspect', {
        headers: {
          Authorization: 'Bearer ' + token
        },
        'method': 'post',
        'contentType': 'application/json',
        'payload': JSON.stringify(request, null, 2)
      });

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

      var oldCoverageState = value.coverageState;
      var newCoverageState = json.inspectionResult.indexStatusResult.coverageState;

      if (oldCoverageState != newCoverageState) {
        changes.push(value.url + ' indexStatusResult changed to ' + newCoverageState + ' from ' + oldCoverageState);
      }

      value.verdict = json.inspectionResult.indexStatusResult.verdict;
      value.coverageState = newCoverageState;
      value.robotsTxtState = json.inspectionResult.indexStatusResult.robotsTxtState;
      value.indexingState = json.inspectionResult.indexStatusResult.indexingState;
      value.lastCrawlTime = json.inspectionResult.indexStatusResult.lastCrawlTime;
      value.pageFetchState = json.inspectionResult.indexStatusResult.pageFetchState;
      value.googleCanonical = json.inspectionResult.indexStatusResult.googleCanonical;
      value.userCanonical = json.inspectionResult.indexStatusResult.userCanonical;
    } catch (err) {
      Logger.log('Error processing url ' + err);
    }
  }

  if (changes.length > 0) {
    MailApp.sendEmail(AlertEmail, 'Google Search Console Monitor found changes on ' + new Date(), changes.join('\r\n'));
  }
}

function updatePagesFromSitemap(pages) {
  var sitemapXml = UrlFetchApp.fetch(SitemapUrl).getContentText();
  var sitemapDoc = XmlService.parse(sitemapXml);
  var root = sitemapDoc.getRootElement();
  var ns = XmlService.getNamespace('http://www.sitemaps.org/schemas/sitemap/0.9');

  var entries = root.getChildren('url', ns);
  for (var i = 0; i < entries.length; i++) {
    var loc = entries[i].getChild('loc', ns).getText();
    var lastmod = ' ';
    var lastmodElement = entries[i].getChild('lastmod', ns);
    if (lastmodElement) {
      lastmod = lastmodElement.getText();
    }

    if (!pages.has(loc)) {
      var page = {};
      page.url = loc;
      page.lastmod = lastmod;
      page.verdict = ' ';
      page.coverageState = 'Added from sitemap';
      page.robotsTxtState = ' ';
      page.indexingState = ' ';
      page.lastCrawlTime = ' ';
      page.pageFetchState = ' ';
      page.googleCanonical = ' ';
      page.userCanonical = ' ';
      page.checkDay = getRandomInt(7);
      pages.set(loc, page);
    }
  }
}

function readPages(pages) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('gsc');
  var lastRow = sheet.getLastRow();
  for (var row = 2; row <= lastRow; row++) {
    var range = sheet.getRange(row, 1, 1, 11).getValues();
    var page = {};
    page.url = range[0][0];
    page.lastmod = range[0][1];
    page.verdict = range[0][2];
    page.coverageState = range[0][3];
    page.robotsTxtState = range[0][4];
    page.indexingState = range[0][5];
    page.lastCrawlTime = range[0][6];
    page.pageFetchState = range[0][7];
    page.googleCanonical = range[0][8];
    page.userCanonical = range[0][9];
    page.checkDay = range[0][10];
    pages.set(page.url, page);
  }
}

function writePages(pages) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('gsc');
  sheet.clear();
  sheet.appendRow(['URL',
    'Last Modified',
    'Verdict',
    'Coverage State',
    'ROBOTS.TXT State',
    'Indexing State',
    'Last Crawl Time',
    'Page Fetch State',
    'Google Canonical',
    'User Canonical',
    'Check Day']);

  for (const [key, value] of pages) {
    sheet.appendRow([value.url,
    value.lastmod,
    value.verdict,
    value.coverageState,
    value.robotsTxtState,
    value.indexingState,
    value.lastCrawlTime,
    value.pageFetchState,
    value.googleCanonical,
    value.userCanonical,
    value.checkDay]);
  }
}

function getRandomInt(max) {
  return Math.floor(Math.random() * max);
}

1-) Open appscripts and then go to project settings, enable show "appsscript.json" manifest file in the editor
2-)Go to Editor and open appsscript.json replace it with following
{
"timeZone": "America/New_York",
"exceptionLogging": "STACKDRIVER",
"oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/webmasters", "https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/script.send_mail"],
"runtimeVersion": "V8"
}
3-) Go to settings again and enter your Project Number (that you are using for search console API calls) which you can find it on Google Cloud Platform Dashboard.
4-) Fill
var AlertEmail = '';
var SitemapUrl = '';
var SearchConsoleProperty = '';
5-) Thats all ( ͡❛ ͜ʖ ͡❛)✊ run it via sheet ui (Update Data)