Getting JavaScript file from Google Sheets

In my google sheet I entered below formulas in B1 and B2

B1 = "name = " & "'" & A1 & "' ;"

B2 = "city = " & "'" & A2 & "' ;"

Then these Cells become valid JS statements name = 'Prabhu Paul' ; and city = 'India' ;

Then I published the Sheet in .csv format, below is the URL.

https://docs.google.com/spreadsheets/d/e/2PACX-1vSQIORejF59NZEyEigMfHFpNcs-XCjKyUbTSrcWJcR0Mn3DO6oSHWeGfm3XvnqQBj1ipMI00I-r5qH7/pub?gid=0&single=true&range=b1:b2&output=csv

And now I used this URL as the source file for my Script.

The script added to site header by putting below code in my wordpress functions.php file

function ti_custom_javascript() {
    ?>
        <script type="text/javascript" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSQIORejF59NZEyEigMfHFpNcs-XCjKyUbTSrcWJcR0Mn3DO6oSHWeGfm3XvnqQBj1ipMI00I-r5qH7/pub?gid=0&single=true&range=b1:b2&output=csv"></script>
    <?php
}
add_action('wp_head', 'ti_custom_javascript');

Then I tried to retrieve those variables by below way in my webpage JS by Sheets

Below is the Custom HTML Block Code in my Page

<p>Hi, This is <span id="name"></span>, and I'm from <span id="city"></span> </p>

<script>
  document.getElementById("name").innerHTML = name ;
  document.getElementById("city").innerHTML = city ;
</script>

Here in code I tried to get the variables named name and city The value of the name variable is Prabhu Paul The value of the city variable is India

Actually my Paragraph element’s innerHTML should like below

Hi, This is Prabhu Paul, and I’m from India

But it’s not returning the values, how to fix this ..?

Answer

In this case, as a workaround, how about using Web Apps as a wrapper? The sample script is as follows.

Usage:

1. Create Google Apps Script project.

Please create a Google Apps Script project. When you access https://script.google.com/ and create a new project, you can create a Google Apps Script project of a standalone type. Please set the title of the Google Apps Script project.

2. Sample script.

Please copy and paste the following script to the script editor of the created Google Apps Script project.

const doGet = _ => ContentService.createTextOutput(UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/e/2PACX-1vSQIORejF59NZEyEigMfHFpNcs-XCjKyUbTSrcWJcR0Mn3DO6oSHWeGfm3XvnqQBj1ipMI00I-r5qH7/pub?gid=0&single=true&range=b1:b2&output=csv").getContentText()).setMimeType(ContentService.MimeType.JAVASCRIPT);

In this case, your URL is used.

3. Deploy Web Apps.

The detailed information can be seen in the official document.

Please set this using the new IDE of the script editor.

1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
2. Please click “Select type” -> “Web App”.
3. Please input the information about the Web App in the fields under “Deployment configuration”.
4. Please select “Me” for “Execute as”.
5. Please select “Anyone” for “Who has access”.
6. Please click “Deploy” button.
7. Copy the URL of the Web App. It’s like https://script.google.com/macros/s/###/exec. This URL is used for your HTML.

4. Testing.

Here, your Custom Javascript function in functions.php file was modified. Please set your Web Apps URL as follows.

function ti_custom_javascript() {
    ?>
        <script type="text/javascript" src="https://script.google.com/macros/s/###/exec"></script>
    <?php
}
add_action('wp_head', 'ti_custom_javascript');

And here, your HTML sample (not modified).

<p>Hi, This is <span id="name"></span>, and I'm from <span id="city"></span> </p>
 
<script>
  document.getElementById("name").innerHTML = name ;
  document.getElementById("city").innerHTML = city ;
</script>

Note:

• When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

• You can see the detail of this in my report “Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)“.

• In this answer, your Spreadsheet is used. Of course, you can directly set the script in Web Apps.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script (Author: me)