Chiamata a Google Services

Questo è un semplice esempio che mostra come usare i servizi Google, ad esempio Google Drive, Fogli e Email.

L'esempio:

  • Legge un file da Google Drive, che contiene un singolo valore moltiplicatore di offerta;
  • Legge un foglio di calcolo contenente id parole chiave e usa gli ID per ottenere le parole chiave e moltiplicare le offerte delle parole chiave per il moltiplicatore di offerta;
  • Scrive i valori di offerta nuovi e precedenti nel foglio di calcolo;
  • Invia una notifica tramite posta elettronica che indica che le offerte sono state aggiornate.

Prima di usare questo esempio, vedere Autenticazione con Google Services per le opzioni su come ottenere un token di accesso o un token di aggiornamento da usare in questo esempio.

function main() {

  // Set these fields based on the option you chose for getting an access token.
  const credentials = {
    accessToken: '',
    clientId: 'your-client-id',
    clientSecret: 'your-client-secret',
    refreshToken: 'your-refresh-token'
  };
 
  // To get a fileId or spreadsheetId, please see
  // https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id.
 
  // The file must contain a single bid multiplier value (for example,1.1),
  // which is used to update keyword bids.
  const fileId = 'your-file-id';
 
  // The spreadsheet must contain 3 valid keyword IDs in cells A2, A3, and A4.
  const spreadsheetId = 'your-spreadsheet-id';

  // The email address to send a notification email to at the end of the script.
  const notificationEmail = 'your-notification-email';
 
  var driveApi = GoogleApis.createDriveService(credentials);
 
  // Read bid multiplier from the file.
  // Reference: https://developers.google.com/drive/api/v3/reference/files/export
  var bidMultiplier = driveApi.files.export({ fileId: fileId, mimeType: 'text/plain' }).body;
 
  Logger.log(`read bid multiplier ${bidMultiplier}`);
 
  var sheetsApi = GoogleApis.createSheetsService(credentials);
 
  // Read keyword IDs from the spreadsheet.
  // Reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
  // A1 notation documentation: https://developers.google.com/sheets/api/guides/concepts#a1_notation
  var keywordIds = sheetsApi.spreadsheets.values.get({
    spreadsheetId: spreadsheetId,
    range: 'A2:A4'
  }).result.values.map(x => x[0]);
 
  Logger.log(`read ${keywordIds.length} keyword ids`);
 
  // Fetch keywords by IDs.
  var keywords = AdsApp.keywords().withIds(keywordIds).get();
 
  var oldBids = [];
  var newBids = [];
 
  // Update keyword bids, save the old and new bid values into arrays.
  while (keywords.hasNext()) {
    var kw = keywords.next();
 
    var oldBid = kw.bidding().getCpc();
 
    var newBid = oldBid * bidMultiplier;
 
    oldBids.push(oldBid);
    newBids.push(newBid);
 
    kw.bidding().setCpc(newBid);
  }
 
  // Write the old and new bid values back to the spreadsheet.
  // Reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
  var updateResponse = sheetsApi.spreadsheets.values.batchUpdate({ spreadsheetId: spreadsheetId }, {
    data: [
      { range: 'B2:B4', values: oldBids.map(x => [x]) },
      { range: 'C2:C4', values: newBids.map(x => [x]) }
    ],
    valueInputOption: 'USER_ENTERED'
  });
 
  Logger.log(`updated ${updateResponse.result.totalUpdatedCells} cells`);
 
  var gmailApi = GoogleApis.createGmailService(credentials);  
 
  var email = [
    `To: ${notificationEmail}`,
    'Subject: Google Services script',
    '',
    `You script ran successfully ✓ and updated ${updateResponse.result.totalUpdatedCells} cells.`
  ].join('\n');
 
  // Send the notification email.
  // Reference: https://developers.google.com/gmail/api/v1/reference/users/messages/send
  var sendResponse = gmailApi.users.messages.send({ userId: 'me' }, { raw: Base64.encode(email) });
 
  Logger.log(`sent email thread ${sendResponse.result.threadId}`);
}
 
var GoogleApis;
(function (GoogleApis) {
  function createSheetsService(credentials) {
    return createService("https://sheets.googleapis.com/$discovery/rest?version=v4", credentials);
  }
  GoogleApis.createSheetsService = createSheetsService;
 
  function createDriveService(credentials) {
    return createService("https://www.googleapis.com/discovery/v1/apis/drive/v3/rest", credentials);
  }
  GoogleApis.createDriveService = createDriveService;
 
  function createGmailService(credentials) {
    return createService("https://www.googleapis.com/discovery/v1/apis/gmail/v1/rest", credentials);
  }
  GoogleApis.createGmailService = createGmailService;
 
  // Creation logic based on https://developers.google.com/discovery/v1/using#usage-simple
  function createService(url, credentials) {
    var content = UrlFetchApp.fetch(url).getContentText();
    var discovery = JSON.parse(content);
    var baseUrl = discovery['rootUrl'] + discovery['servicePath'];
    var accessToken = getAccessToken(credentials);
    var service = build(discovery, {}, baseUrl, accessToken);
    return service;
  }
 
  function createNewMethod(method, baseUrl, accessToken) {
    return (urlParams, body) => {
      var urlPath = method.path;
      var queryArguments = [];
      for (var name in urlParams) {
        var paramConfg = method.parameters[name];
        if (!paramConfg) {
          throw `Unexpected url parameter ${name}`;
        }
        switch (paramConfg.location) {
          case 'path':
            urlPath = urlPath.replace('{' + name + '}', urlParams[name]);
            break;
          case 'query':
            queryArguments.push(`${name}=${urlParams[name]}`);
            break;
          default:
            throw `Unknown location ${paramConfg.location} for url parameter ${name}`;
        }
      }
      var url = baseUrl + urlPath;
      if (queryArguments.length > 0) {
        url += '?' + queryArguments.join('&');
      }
      var httpResponse = UrlFetchApp.fetch(url, { contentType: 'application/json', method: method.httpMethod, payload: JSON.stringify(body), headers: { Authorization: `Bearer ${accessToken}` }, muteHttpExceptions: true });
      var responseContent = httpResponse.getContentText();
      var responseCode = httpResponse.getResponseCode();
      var parsedResult;
      try {
        parsedResult = JSON.parse(responseContent);
      } catch (e) {
        parsedResult = false;
      }
      var response = new Response(parsedResult, responseContent, responseCode);
      if (responseCode >= 200 && responseCode <= 299) {
        return response;
      }
      throw response;
    }
  }
 
  function Response(result, body, status) {
    this.result = result;
    this.body = body;
    this.status = status;
  }
  Response.prototype.toString = function () {
    return this.body;
  }
 
  function build(discovery, collection, baseUrl, accessToken) {
    for (var name in discovery.resources) {
      var resource = discovery.resources[name];
      collection[name] = build(resource, {}, baseUrl, accessToken);
    }
    for (var name in discovery.methods) {
      var method = discovery.methods[name];
      collection[name] = createNewMethod(method, baseUrl, accessToken);
    }
    return collection;
  }
 
  function getAccessToken(credentials) {
    if (credentials.accessToken) {
      return credentials.accessToken;
    }
    var tokenResponse = UrlFetchApp.fetch('https://www.googleapis.com/oauth2/v4/token', { method: 'post', contentType: 'application/x-www-form-urlencoded', muteHttpExceptions: true, payload: { client_id: credentials.clientId, client_secret: credentials.clientSecret, refresh_token: credentials.refreshToken, grant_type: 'refresh_token' } });    
    var responseCode = tokenResponse.getResponseCode(); 
    var responseText = tokenResponse.getContentText(); 
    if (responseCode >= 200 && responseCode <= 299) {
      var accessToken = JSON.parse(responseText)['access_token'];
      return accessToken;
    }    
    throw responseText;  
  }
})(GoogleApis || (GoogleApis = {}));
 
// Base64 implementation from https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/master/lib/msal-core/src/Utils.ts
class Base64 {
  static encode(input) {
    const keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
    let output = "";
    let chr1, chr2, chr3, enc1, enc2, enc3, enc4;
    var i = 0;
    input = this.utf8Encode(input);
    while (i < input.length) {
      chr1 = input.charCodeAt(i++);
      chr2 = input.charCodeAt(i++);
      chr3 = input.charCodeAt(i++);
      enc1 = chr1 >> 2;
      enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
      enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
      enc4 = chr3 & 63;
      if (isNaN(chr2)) {
        enc3 = enc4 = 64;
      }
      else if (isNaN(chr3)) {
        enc4 = 64;
      }
      output = output + keyStr.charAt(enc1) + keyStr.charAt(enc2) + keyStr.charAt(enc3) + keyStr.charAt(enc4);
    }
    return output.replace(/\+/g, "-").replace(/\//g, "_").replace(/=+$/, "");
  }
  static utf8Encode(input) {
    input = input.replace(/\r\n/g, "\n");
    var utftext = "";
    for (var n = 0; n < input.length; n++) {
      var c = input.charCodeAt(n);
      if (c < 128) {
        utftext += String.fromCharCode(c);
      }
      else if ((c > 127) && (c < 2048)) {
        utftext += String.fromCharCode((c >> 6) | 192);
        utftext += String.fromCharCode((c & 63) | 128);
      }
      else {
        utftext += String.fromCharCode((c >> 12) | 224);
        utftext += String.fromCharCode(((c >> 6) & 63) | 128);
        utftext += String.fromCharCode((c & 63) | 128);
      }
    }
    return utftext;
  }
}