3

I'm using the google sheet webscript to manipulate spreadsheet data and I want to use the following function to encrypt certain cells:

var encrypted = CryptoJS.AES.encrypt("message", "Secret key");

There's an option to add libraries to the Google Sheet webscript but I have no idea how to get a library installed. According to the Google documentation you need the project key/script ID in order to use the library, but I have not been able to find this kind of information.

Can someone assist in how to actually import this CryptoJS library to use in the webscript.

enter image description here

enter image description here

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Michael T
  • 756
  • 3
  • 13
  • 31
  • 1
    Hello @MichaelTolsma, you might not need the `CryptoJS` library at all because if you want to encrypt certain cells you might make use of some of the methods presented in this documentation [here](https://developers.google.com/apps-script/reference/utilities/utilities). Cheers! – ale13 Mar 20 '20 at 14:59
  • 1
    Related: https://stackoverflow.com/a/63647410/ – TheMaster Aug 29 '20 at 13:31

1 Answers1

8

Issue and workaround:

Unfortunately, in the current stage, it seems that there are no built-in methods for directly achieving the AES encryption in Google Apps Script methods.

So in this case, how about the following workarounds?

Pattern 1:

In this pattern, crypto-js is used.

Usage:

1. Get crypto-js:

Please access to https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.2/rollups/aes.js. And copy and paste the script to the script editor.

2. Sample script:

After the copy and paste crypto-js, please copy and paste the following sample script.

function myFunction() {
  var key = "sampleSecretKey";
  var value = "sampleMessage";

  var encryptedMessage = CryptoJS.AES.encrypt(value, key).toString();
  var decryptedMessage = CryptoJS.AES.decrypt(encryptedMessage, key).toString(CryptoJS.enc.Utf8);

  Logger.log(encryptedMessage);
  Logger.log(decryptedMessage);
}
  • When you run the function of myFunction(), the encrypted and decrypted values are returned.

Pattern 2: Updated on April 7, 2022.

In this pattern, "cCryptoGS" which is a Google Apps Script library is used.

Usage:

1. Install Google Apps Script library:

The project key for installing the library is 1IEkpeS8hsMSVLRdCMprij996zG6ek9UvGwcCJao_hlDMlgbWWvJpONrs.

Please install the GAS library using this project key.

2. Sample script:

function myFunction() {
  var key = "sampleSecretKey";
  var value = "sampleMessage";

  var cipher = new cCryptoGS.Cipher(key, 'aes');
  var encryptedMessage = cipher.encrypt(value);
  var decryptedMessage = cipher.decrypt(encryptedMessage);

  Logger.log (encryptedMessage);
  Logger.log (decryptedMessage);
}
  • When you run the function of myFunction, the encrypted and decrypted values are returned.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    I can't get pattern 1 to work, but pattern 2 seems to be a good option. Thank you! – Michael T Mar 23 '20 at 09:37
  • @Michael Tolsma Thank you for replying. I'm glad your issue was resolved. – Tanaike Mar 24 '20 at 00:28
  • Would you perhaps know if there is an "iv" value for this second method? – Michael T Mar 24 '20 at 09:43
  • @Michael Tolsma Unfortunately, I'm not sure about it. This is due to my poor skill. I deeply apologize for this. For example, how about asking it to the author of the library? – Tanaike Mar 24 '20 at 22:32
  • I ended up having to take the entire source code of AES part and add it as a separate file in my GSheet webscript. That way I could access CryptoJS methods directly as opposed to going through cCryotoGS. – Michael T Mar 25 '20 at 06:13
  • The first project key is not working anymore, but thanks for your answer Tanaike. Very helpful, as usual – Waxim Corp Apr 07 '22 at 11:18
  • 1
    @Waxim Corp Thank you for your comment. From your comment, when I tested the "Pattern 2", I could confirm that the script works fine. But, in the current stage, it is required to use the project ID of `1IEkpeS8hsMSVLRdCMprij996zG6ek9UvGwcCJao_hlDMlgbWWvJpONrs` for installing the library. So I updated it. – Tanaike Apr 07 '22 at 12:32