Negative keyword list

function main() {
  // Replace with your Google Spreadsheet URL
  const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
  const SHEET_NAME = 'Negative keyword list'; // Define the sheet name
  
  // Open the spreadsheet and get the sheet
  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  let sheet = spreadsheet.getSheetByName(SHEET_NAME);
  
  // If the sheet doesn't exist, create it
  if (!sheet) {
    sheet = spreadsheet.insertSheet(SHEET_NAME);
  } else {
    // Clear existing data if the sheet already exists
    sheet.clear();
  }
  
  // Set headers
  sheet.getRange(1, 1).setValue('Campaign Name');
  sheet.getRange(1, 2).setValue('Ad Group Name');
  sheet.getRange(1, 3).setValue('Negative Keyword');
  sheet.getRange(1, 4).setValue('Match Type');
  
  let row = 2; // Start from the second row to write data
  
  // Get all negative keyword lists at the campaign level
  const campaignIterator = AdsApp.campaigns().get();
  while (campaignIterator.hasNext()) {
    const campaign = campaignIterator.next();
    const negativeKeywords = campaign.negativeKeywords().get();
    
    while (negativeKeywords.hasNext()) {
      const negativeKeyword = negativeKeywords.next();
      sheet.getRange(row, 1).setValue(campaign.getName());
      sheet.getRange(row, 2).setValue('N/A'); // No specific ad group at campaign level
      sheet.getRange(row, 3).setValue(negativeKeyword.getText());
      sheet.getRange(row, 4).setValue(negativeKeyword.getMatchType());
      row++;
    }
  }
  
  // Get all negative keyword lists at the ad group level
  const adGroupIterator = AdsApp.adGroups().get();
  while (adGroupIterator.hasNext()) {
    const adGroup = adGroupIterator.next();
    const negativeKeywords = adGroup.negativeKeywords().get();
    
    while (negativeKeywords.hasNext()) {
      const negativeKeyword = negativeKeywords.next();
      sheet.getRange(row, 1).setValue(adGroup.getCampaign().getName());
      sheet.getRange(row, 2).setValue(adGroup.getName());
      sheet.getRange(row, 3).setValue(negativeKeyword.getText());
      sheet.getRange(row, 4).setValue(negativeKeyword.getMatchType());
      row++;
    }
  }
  
  Logger.log(`Export complete. Data has been written to the sheet: ${SHEET_NAME}`);
}