Getting JavaScript file from Google Sheets-CodePudding

In my google sheet I make the Cell B1 to become a valid JS staement by below formula.

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

Then cell B1 becomes valid statement name = 'Raj Paul' ;

By publishing the sheet, I tried to make it as a script file for my site like below

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

Now I used this as a script source file and added to my wp functions.php file by below way

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&output=csv"></script>
    <?php
}
add_action('wp_head', 'ti_custom_javascript');

Then I tried to retrieve that variable 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="shvar"></span> </p>

<script>
function shvar () {
  document.getElementById("shvar").innerHTML = name ; //or globalThis.name
}
shvar(); //calling function 
</script>

Here in code I tried to get a variable named name The value of the variable is Raj Paul

Actually my Paragraph element should like below Hi, This is Raj Paul

But it's not returning the value, how to get this ..?

CodePudding user response：

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&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.

On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
Please click "Select type" -> "Web App".
Please input the information about the Web App in the fields under "Deployment configuration".
Please select "Me" for "Execute as".
Please select "Anyone" for "Who has access".
Please click "Deploy" button.
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 HTML sample was modified. Please set your Web Apps URL as follows.

<p>Hi, This is <span id="shvar"></span> </p>
<script type="text/javascript" src="https://script.google.com/macros/s/###/exec"></script>
<script>
console.log(name); // You can see the value of a variable in Javascript provided from Web Apps at the console.
document.getElementById("shvar").innerHTML = name;
</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.