GAS サービスアカウント　OAuth2 なかなかはまったのでメモ。

function findNonBase64Char(PRIVATE_KEY) {
  var base64Chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';
  for (var i = 0; i < PRIVATE_KEY.length; i++) {
    if (base64Chars.indexOf(PRIVATE_KEY[i]) === -1) {
      Logger.log('Non-Base64 character found at position ' + i + ': ' + PRIVATE_KEY[i]);
    }
  }
}

function test_auth(){
  // Your Google Service Account's email and private key
//  var PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY');
 // var PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY').trim();
  var PRIVATE_KEY = JSON.parse('"' + PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY').trim() + '"');


  //Logger.log(PRIVATE_KEY);
  var CLIENT_EMAIL = PropertiesService.getScriptProperties().getProperty('CLIENT_EMAIL');

  if (typeof PRIVATE_KEY !== 'string') {
    Logger.log('PRIVATE_KEY is not a string');
    return;
  }
  var match = PRIVATE_KEY.match(/-----BEGIN PRIVATE KEY-----(.*)-----END PRIVATE KEY-----/s);
  if (!match) {
    Logger.log('PRIVATE_KEY does not contain a key');
    return;
  }
  var keyBody = match[1];
  if (!/^[A-Za-z0-9+/= \n]+$/.test(keyBody)) {
  Logger.log('The body of PRIVATE_KEY is not a Base64-encoded string');
  findNonBase64Char(keyBody);
  
  return;
}



  

  // OAuth2 service
  var service = OAuth2.createService('Google Sheets')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setPrivateKey(PRIVATE_KEY)
    .setIssuer(CLIENT_EMAIL)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope('https://www.googleapis.com/auth/spreadsheets');

  // Reset the service
  service.reset();

  if (!service.hasAccess()) {
    var error = service.getLastError();
    Logger.log('Authentication failed: ' + error);
    return;
  }

  // Spreadsheet ID
  var spreadsheetId = 'これもプロパティから取得すべきかな？';

  // Google Sheets API URL
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '?includeGridData=true';

  // HTTP options
  var params = {
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    }
  };

  // Fetch spreadsheet data
  var response = UrlFetchApp.fetch(url, params);
  var data = JSON.parse(response.getContentText());

  // Get the number of columns in the 'log' sheet
  var sheetData = data.sheets.find(function(sheet) {
    return sheet.properties.title === 'log';
  });

  if (sheetData) {
    var cols = sheetData.data[0].rowData[0].values.length;
    Logger.log(cols);
  } else {
    Logger.log('Sheet not found');
  }

  
}