DataForSEO_GetKWRank.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('DataForSEO')
    .addItem('Set Credentials', 'showCredentialsPrompt')
    .addToUi();
}

function showCredentialsPrompt() {
  const ui = SpreadsheetApp.getUi();
  const userProps = PropertiesService.getUserProperties();
  const currentCredentials = userProps.getProperty('DATAFORSEO_CREDENTIALS') || '';

  const html = HtmlService.createHtmlOutput(`
    <form id="credentialsForm">
      <label for="login">Login:</label><br>
      <input type="text" id="login" name="login"><br>
      <label for="password">Password:</label><br>
      <input type="password" id="password" name="password"><br><br>
      <input type="submit" value="Save">
    </form>
    <p>Current encoded credentials: <span id="currentCreds">${currentCredentials}</span></p>
    <script>
      document.getElementById('credentialsForm').addEventListener('submit', function(e) {
        e.preventDefault();
        var login = this.login.value;
        var password = this.password.value;
        if (login && password) {
          var encoded = Utilities.base64Encode(login + ':' + password);
          google.script.run.withSuccessHandler(function() {
            document.getElementById('currentCreds').textContent = encoded;
          }).saveCredentials(encoded);
        }
      });
    </script>
  `)
  .setWidth(300)
  .setHeight(200);

  ui.showModalDialog(html, 'Set DataForSEO Credentials');
}

function saveCredentials(encodedCredentials) {
  PropertiesService.getUserProperties().setProperty('DATAFORSEO_CREDENTIALS', encodedCredentials);
}

function position(location_name, keyword, target) {
  const CREDENTIALS = PropertiesService.getUserProperties().getProperty('DATAFORSEO_CREDENTIALS');
  if (!CREDENTIALS) {
    return 'Error: No api credentials set (Use your login email + Your API password)';
  }
  
  const API_URL = 'https://api.dataforseo.com/v3/serp/google/organic/live/advanced';
  const postData = [{
    "language_name": "English",
    "location_name": location_name,
    "keyword": keyword,
    "device": "desktop",
    "os": "windows",
    "depth": 100
  }];
  
  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Basic ' + CREDENTIALS
    },
    'payload': JSON.stringify(postData),
    'muteHttpExceptions': true
  };
  
  try {
    const response = UrlFetchApp.fetch(API_URL, options);
    const httpCode = response.getResponseCode();
    const result = JSON.parse(response.getContentText());
    
    if (httpCode !== 200) {
      return `HTTP Error: ${httpCode}. Message: ${result.status_message || 'Unknown error'}`;
    }
    
    if (result.tasks && result.tasks[0].status_code === 20000) {
      const items = result.tasks[0].result[0].items;
      for (let i = 0; i < items.length; i++) {
        if (items[i].type === 'organic' && items[i].domain === target) {
          return i + 1;
        }
      }
      return 'Not found in top 100 results';
    } else {
      return `API Error: ${result.tasks[0].status_code}. Message: ${result.tasks[0].status_message}`;
    }
  } catch (error) {
    return `Script Error: ${error.toString()}`;
  }
  // Script by Daniel Emery – Bang Digital
}