多个 Google 表格的分离脚本

huangapple go评论72阅读模式
英文:

Separate script for multiple google sheets

问题

I have a Google sheet. There are two sheets named "Subjects" and "Subjects_Classes."

I have written a script for the "Subjects" sheet as follows:

const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/.../edit?usp=sharing")
const sheet = ss.getSheetByName("Subjects")
function doGet(e){
  let obj = {};
  let data = sheet.getRange("A1:B1000").getValues();
  obj.content = data;
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

I am processing the same with JavaScript in HTML as follows, and it is working perfectly:

fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec')
    .then(res => res.json())
    .then(data => {
        let subjectsdiv = "";
        data.content.forEach(item => {
            if (item[0] === "") {
                // skip to the next iteration if item[0] is empty
                return;
            } else {
                subjectsdiv += `<div class="col">
                                  <div class="card h-100">
                                    <img src="#" class="card-img-top img-responsive" alt="...">
                                    <div class="card-body">
                                       <h5 class="card-title">${item[0]}</h5>
                                       <p class="card-text">Explore the many ${item[0]} resources available online to enhance your understanding of the subject</p>
                                    </div>
                                    <a href="${item[1]}" class="btn btn-primary stretched-link" target="_blank">Read</a>
                                  </div>
                                </div>`;
            }
        });
        document.querySelector("#Subjects").innerHTML = subjectsdiv;
    });

Now, I need another JSON for the "Subjects_Classes" sheet as follows. Hence, the sheet and columns are different:

const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/ ... /edit?usp=sharing")
const sheet = ss.getSheetByName("Subjects_Classes")
function doGet(e){
  let obj = {};
  let data = sheet.getRange("A1:C1000").getValues();
  obj.content = data;
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

You can use a similar JavaScript code to access this data in HTML, making necessary changes to the variable names and HTML structure as needed.

英文:

I have google sheet. There are two sheet named "Subjects" and "Subjects_Classes"

I have written a script for sheet "Subjects" as follows:

const ss = SpreadsheetApp.openByUrl(&quot;https://docs.google.com/spreadsheets/d/.../edit?usp=sharing&quot;)
const sheet = ss.getSheetByName(&quot;Subjects&quot;)
function doGet(e){
  let obj = {};
  let data = sheet.getRange(&quot;A1:B1000&quot;).getValues();
  obj.content = data;
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON)
 
}

I am processing the same with Javascript in html as follows: It is working perfectly.

fetch(&#39;https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec&#39;)
    .then(res =&gt; res.json())
    .then(data =&gt; {
        let subjectsdiv = &quot;&quot;;
        data.content.forEach(item =&gt; {
            if (item[0] === &quot;&quot;) {
                // skip to the next iteration if item[0] is empty
                return;
            } else {
                subjectsdiv += `&lt;div class=&quot;col&quot;&gt;
                                  &lt;div class=&quot;card h-100&quot;&gt;
                                    &lt;img src=&quot;#&quot; class=&quot;card-img-top img-responsive&quot; alt=&quot;...&quot;&gt;
                                    &lt;div class=&quot;card-body&quot;&gt;
                                       &lt;h5 class=&quot;card-title&quot;&gt;${item[0]}&lt;/h5&gt;
                                       &lt;p class=&quot;card-text&quot;&gt;Explore the many ${item[0]} resources available online to enhance your understanding of the subject&lt;/p&gt;
                                    &lt;/div&gt;
                                    &lt;a href=&quot;${item[1]}&quot; class=&quot;btn btn-primary stretched-link&quot; target=&quot;_blank&quot;&gt;Read&lt;/a&gt;
                                  &lt;/div&gt;
                                &lt;/div&gt;`;
            }
        });
        document.querySelector(&quot;#Subjects&quot;).innerHTML = subjectsdiv;
    });
    
&lt;/script&gt;

Now I need another JSON for sheet "Subjects_Classes" as follows: Hence Sheet and columns are different.

const ss = SpreadsheetApp.openByUrl(&quot;https://docs.google.com/spreadsheets/d/ ... /edit?usp=sharing&quot;)
const sheet = ss.getSheetByName(&quot;Subjects_Classes&quot;)
function doGet(e){
  let obj = {};
  let data = sheet.getRange(&quot;A1:C1000&quot;).getValues();
  obj.content = data;
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON)
 
}

I need help to create script for another sheet. Also need help to access the same in html (if changes is required).

答案1

得分: 1

虽然我不确定我是否正确理解了您期望的情况,不过以下修改如何?

在这个修改中,电子表格和范围是使用查询参数更改的。

Google Apps Script 部分:

请将您的电子表格 ID 和范围设置为 object

function doGet(e) {
  const object = {
    pattern1: { spreadsheetId: "###", range: "'Subjects'!A1:B1000" }, // 请设置您的电子表格 ID 和范围。
    pattern2: { spreadsheetId: "###", range: "'Subjects_Classes'!A1:C1000" }, // 请设置您的电子表格 ID 和范围。
  };
  if (object[e.parameter.pattern]) {
    const { spreadsheetId, range } = object[e.parameter.pattern];
    let obj = {};
    let data = SpreadsheetApp.openById(spreadsheetId).getRange(range).getValues();
    obj.content = data;
    return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
  }
  return ContentService.createTextOutput(JSON.stringify({content: []})).setMimeType(ContentService.MimeType.JSON);
}

Javascript 部分:

请将包含查询参数的 Web Apps URL 设置为 ?pattern=pattern1?pattern=pattern2,如 https://script.google.com/macros/s/###/exec?pattern=pattern1

从:

fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec')

到:

fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern1')

fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern2')
  • 您可以通过查询参数从 'Subjects'!A1:B1000'Subjects_Classes'!A1:C1000 中检索值。

注意:

英文:

Although I'm not sure whether I could correctly understand your expected situation, how about the following modification?

In this modification, the spreadsheet and range are changed using a query parameter.

Google Apps Script side:

Please set your Spreadsheet ID and range to object.

function doGet(e) {
  const object = {
    pattern1: { spreadsheetId: &quot;###&quot;, range: `&#39;Subjects&#39;!A1:B1000` }, // Please set your Spreadsheet ID and range.
    pattern2: { spreadsheetId: &quot;###&quot;, range: `&#39;Subjects_Classes&#39;!A1:C1000` }, // Please set your Spreadsheet ID and range.
  };
  if (object[e.parameter.pattern]) {
    const { spreadsheetId, range } = object[e.parameter.pattern];
    let obj = {};
    let data = SpreadsheetApp.openById(spreadsheetId).getRange(range).getValues();
    obj.content = data;
    return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON)
  }
  return ContentService.createTextOutput(JSON.stringify({content: []})).setMimeType(ContentService.MimeType.JSON)
}

Javascript side:

Please set your Web Apps URL including the query parameter like ?pattern=pattern1 or ?pattern=pattern2 like https://script.google.com/macros/s/###/exec?pattern=pattern1.

From:

fetch(&#39;https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec&#39;)

To:

fetch(&#39;https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern1&#39;)

or

fetch(&#39;https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern2&#39;)
  • You can retrieve the values from &#39;Subjects&#39;!A1:B1000 and &#39;Subjects_Classes&#39;!A1:C1000 by the query parameter.

Note:

huangapple
  • 本文由 发表于 2023年2月18日 23:38:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75494417.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定