! AppScripts

iterate-http-update.js

const API_URL = 'https://example.com';
const AUTH = 'Basic XXXXXXXXXXXXXXXXXXXXXX';

function getContact(phone) {
  const res = UrlFetchApp.fetch(`${API_URL}/contacts/${phone}@c.us`, {
    method : 'get',
    contentType: 'application/json',
    headers : {
      Authorization: AUTH,
    },
  });
  return JSON.parse(res.getContentText());
}

function completeData() {
  const result = {};
  const sheet = SpreadsheetApp.getActive().getSheets()[0];
  const range = sheet.getDataRange();
  const numRows = range.getNumRows();
  // interate all row,
  // but skip header row
  for (let i = 2; i <= numRows; i++) {
    const name = range.getCell(i, 1).getValue();
    const year = range.getCell(i, 3).getValue();
    const campus = range.getCell(i, 4).getValue();
    const phone = range.getCell(i, 8).getValue();
    // convert +62XXXXXXXX to 62XXXXXXXX
    const waPhone = phone[0] == '+' ? phone.substring(1) : phone; 
    let waName = result[waPhone];
    console.log(`${name} (${campus} ${year}): ${waPhone}`);
    if (!result[waPhone]) {
      const contact = getContact(waPhone);
      waName = contact.name || contact.pushname || contact.email || contact.description ||  '-';
      // cache result
      result[waPhone] = waName;
    }
    // update cell value
    range.getCell(i, 10).setValue(waName);
  }
}