sha-256 encryption within Google Sheets using App Scripts

Question

I am trying to encrypt 3 different elements into an authentication token for a http headers for an API. This API is built into Google Sheets, and I can't use anything else at the moment.

The authentication token requires 4 parts:

1. API Key
2. Timestamp in UTC format
3. API Action
4. API Secret Key

In the format of API KEY:TIMESTAMP:API ACTION:API Secret Key

For the purposes of this example, let's say that the

1. API key is test123,
2. UTC Date: Thu, 14 Apr 2011 22:44:22 GMT
3. API action is 'ledger'
4. API Secret key is UAV213Q

When I tested the example using following format "test123:Thu, 14 Apr 2011 22:44:22 GMT:ledger:UAV213Q" in python I got the result 15594d1f608134cbfa3075ecda4664519cd198738b8f5c3ffa2c95272b854199

This is the python script I used

def sha256():
    # tested on Python 3.8.5
    from urllib import parse, request
    import hashlib
    import datetime
    from time import strftime, gmtime

    # credentials and request params
    my_merchant_id = 'apikey'
    api_token = 'test123'
    api_secret_key = 'UAV213Q'
    my_timestamp = strftime("%a, %d %b %Y %H:%M:%S +0000", gmtime())
    api_version = 2.9
    action_verb = 'ledger'

    # set up request params
    data = parse.urlencode({'merchantId': my_merchant_id, 'token': api_token,
                        'version': api_version, 'action': action_verb})

    # authentication
    sig = api_token + ':' + my_timestamp + ':' + action_verb + ':' + api_secret_key
    sig_hash = hashlib.sha256(sig.encode('utf-8')).hexdigest()
    my_headers = {'x-ShareASale-Date': my_timestamp,
                'x-ShareASale-Authentication': sig_hash}

    print(sig_hash)

I've tried using solutions from the following other StackOverFlow questions How do I get Google Apps Script to do SHA-256 encryption?, sha3-256 of a cell text in Google Spreadsheet, all the suggestions.

However, I keep getting the error message "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()."

I have tried referencing a cell that is indirectly references the NOW() by having NOW() in A1 and having B1 =A1, I have even tried to convert it to text by using the TEXT().

The API key needs to have the timestamp to function. I was thinking about having that calculated in the App script itself, since it is a known constant. For example within the encryption script it would hardcode the api token, call the timestamp in utc format and hardcode the api secret key in the correct format and the maybe just the function to add the action so I can make change that so it would be sha256(ledger) and it would incorporate that into the encryption

Answer 1

How about this answer?

Modification points:

• When I saw your python script, I confirmed that the specification shown in your question is different from that of the python script.
  • It seems that Thu, 14 Apr 2011 22:44:22 GMT is Thu, 14 Apr 2011 22:44:22 +0000
  • It seems that it is required to use "SHA_256" of the digest.

Sample script:

When your python script is converted to Google Apps Script, it becomes as follows. Please copy and paste it to the script editor and run the function myFunction at the script editor. By this, you can see the result value at the log.

function myFunction() {
  const api_token = 'test123';
  const api_secret_key = 'UAV213Q';
  const my_timestamp = 'Thu, 14 Apr 2011 22:44:22 +0000';
  const action_verb = 'ledger';

  const value = `${api_token}:${my_timestamp}:${action_verb}:${api_secret_key}`;
  const bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, value);
  const res = bytes.map(byte => ('0' + (byte & 0xFF).toString(16)).slice(-2)).join('');
  console.log(res)
}

Result:

When test123:Thu, 14 Apr 2011 22:44:22 +0000:ledger:UAV213Q is used for above Google Apps Script and your python script, both results are the same as follows.

8c3a6873fe71c402dc1e3ca7bc828712e3dfb7a66ed09feeeca2152dd809df81

Reference:

• computeDigest(algorithm, value)

Added:

Answer for additional question 1:

When you want to retrieve the date string like Thu, 14 Apr 2011 22:44:22 +0000, please use the following script.

const my_timestamp = new Date().toUTCString().replace("GMT", "+0000");

Answer for additional question 2:

When you want to retrieve the value as the upper case, please use the following script. But when I tested your python script, the result value is the lower case. So please be careful this.

function myFunction() {
  const api_token = 'test123';
  const api_secret_key = 'UAV213Q';
  const my_timestamp = 'Thu, 14 Apr 2011 22:44:22 +0000';
  const action_verb = 'ledger';

  const value = `${api_token}:${my_timestamp}:${action_verb}:${api_secret_key}`;
  const bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, value);
  const res = bytes.map(byte => ('0' + (byte & 0xFF).toString(16)).slice(-2)).join('').toUpperCase();
  console.log(res)
}

• In this case, 8C3A6873FE71C402DC1E3CA7BC828712E3DFB7A66ED09FEEECA2152DD809DF81 is obtained.

References:

• toUTCString()
• toUpperCase()