get WordPress URL to the google sheet with sheetwise

googleappscript_wordpress.gs

const websiteUrl = "";

function mainLoop(){
  // createSheets();
  fetchApi( websiteUrl );
}

const headersList = {
 "Accept": "*/*",
 "User-Agent": "Thunder Client (https://www.thunderclient.com)",
 "Authorization": "Basic base64( username:password )"
}

function fetchApi( websiteUrl ) {
  const options = {
    "method" : "GET",
    "headers" : headersList
  };
  let siteName = websiteUrl;
  const API_URL = `${siteName}/wp-json/wp/v2/posts?per_page=100&status=trash&_fields=id,title,link,status`
  // The code below logs the value for every key of the returned map.
  let response = UrlFetchApp.fetch( API_URL, options );
  let apiResponse = response.getContentText();
  const headers = response.getAllHeaders();
  const strtingPage = 1;
  const totalPages = parseInt( headers['x-wp-totalpages'] );
  // const totalPages = 3;
  for( let loopCounter = strtingPage; loopCounter <= totalPages ; loopCounter++ ){
    let response = UrlFetchApp.fetch(`${API_URL}&page=${loopCounter}`, options);
    console.log(`${API_URL}&page=${loopCounter}`)
    let apiResponse = JSON.parse(response.getContentText());
    writeMultipleRows( apiResponse, websiteUrl );
  }
}

function writeMultipleRows( responseData, sheetName) {
 let data = getMultipleRowsData( responseData );
  let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let recordsSheet = activeSpreadsheet.getSheetByName(sheetName);
  if (recordsSheet == null) {
    recordsSheet = activeSpreadsheet.insertSheet();
    recordsSheet.setName(sheetName);
  }
  let lastRow = recordsSheet.getLastRow();
  recordsSheet.getRange(lastRow + 1,1,data.length, data[0].length).setValues(data);
}

function getMultipleRowsData( responseData ) {
 let data = [];
 for(let i =0; i < responseData.length; i++) {
   data.push([ responseData.id,decodeHTMLEntities(responseData[i].title.rendered), responseData[i].link ]);
 }
 return data;
}

function decodeHTMLEntities(text) {
    let entities = [
        ['amp', '&'],
        ['apos', '\''],
        ['#x27', '\''],
        ['#x2F', '/'],
        ['#39', '\''],
        ['#47', '/'],
        ['lt', '<'],
        ['gt', '>'],
        ['nbsp', ' '],
        ['quot', '"'],
        [ "#8217","’"],
        [ "#8220","“"],
        [ "#8221","”"],
        [ "#8216","‘"],
        [ "#038","&"],
    ];
    for (let i = 0, max = entities.length; i < max; ++i) 
        text = text.replace(new RegExp('&'+entities[i][0]+';', 'g'), entities[i][1]);

    return text;
}

wordpress_url_to_google_sheet_.gs

const websiteUrls = [
  "https://techcrunch.com",
  "https://www.capgemini.com",
]

function createSheets(){
  for( let i = 0; i<websiteUrls.length;i++ ) {
    let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let recordsSheet = activeSpreadsheet.getSheetByName(websiteUrls[i]);

    if (recordsSheet == null) {
      recordsSheet = activeSpreadsheet.insertSheet();
      recordsSheet.setName(websiteUrls[i]);
    }
  }
}

function mainLoop(){
  // createSheets();
  for( let mainLoopCounter = 0; mainLoopCounter < websiteUrls.length; mainLoopCounter++ ) {
    fetchApi( websiteUrls[mainLoopCounter] );
  }
}

function fetchApi( arraySiteName ) {
  let siteName = arraySiteName.toLowerCase();
  const API_URL = `${siteName}/wp-json/wp/v2/posts?per_page=100&_fields=title,link,status&page=1`
  // The code below logs the value for every key of the returned map.
  let response = UrlFetchApp.fetch(`${siteName}/wp-json/wp/v2/posts?per_page=100&_fields=title,link,status&page=1`);
  let apiResponse = response.getContentText();
  const headers = response.getAllHeaders();
  const strtingPage = 1;
  const totalPages = parseInt( headers['x-wp-totalpages'] );
  // const totalPages = 3;

  for( let loopCounter = strtingPage; loopCounter <= totalPages ; loopCounter++ ){
    console.log(totalPages)
    let response = UrlFetchApp.fetch(`${siteName}/wp-json/wp/v2/posts?per_page=100&_fields=title,link,status&page=${loopCounter}`);
    console.log(`${siteName}/wp-json/wp/v2/posts?per_page=100&_fields=title,link,status&page=${loopCounter}`)
    console.log(totalPages);
    let apiResponse = JSON.parse(response.getContentText());
    writeMultipleRows( apiResponse, arraySiteName );

  }

}

function writeMultipleRows( responseData, sheetName) {
 let data = getMultipleRowsData( responseData );
  let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let recordsSheet = activeSpreadsheet.getSheetByName(sheetName);

  if (recordsSheet == null) {
    recordsSheet = activeSpreadsheet.insertSheet();
    recordsSheet.setName(sheetName);
  }
  
  let lastRow = recordsSheet.getLastRow();
  recordsSheet.getRange(lastRow + 1,1,data.length, data[0].length).setValues(data);
}

function getMultipleRowsData( responseData ) {
 let data = [];
 for(let i =0; i < responseData.length; i++) {
   data.push([ decodeHTMLEntities(responseData[i].title.rendered), responseData[i].link ]);
 }
 return data;
}

function decodeHTMLEntities(text) {
    let entities = [
        ['amp', '&'],
        ['apos', '\''],
        ['#x27', '\''],
        ['#x2F', '/'],
        ['#39', '\''],
        ['#47', '/'],
        ['lt', '<'],
        ['gt', '>'],
        ['nbsp', ' '],
        ['quot', '"'],
        [ "#8217","’"],
        [ "#8220","“"],
        [ "#8221","”"],
        [ "#8216","‘"],
        [ "#038","&"],
    ];

    for (let i = 0, max = entities.length; i < max; ++i) 
        text = text.replace(new RegExp('&'+entities[i][0]+';', 'g'), entities[i][1]);

    return text;
}