英文:
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("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: 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;
});
</script>
Now I need another JSON for sheet "Subjects_Classes" as follows: Hence 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)
}
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
中检索值。
注意:
-
当您修改了 Google Apps Script 的 Web Apps 时,请将部署作为新版本进行修改。通过这种方式,修改后的脚本会反映在 Web Apps 中。请注意这一点。
-
您可以在我的报告 "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)" 中查看详细信息。
英文:
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: "###", range: `'Subjects'!A1:B1000` }, // Please set your Spreadsheet ID and range.
pattern2: { spreadsheetId: "###", range: `'Subjects_Classes'!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('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec')
To:
fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern1')
or
fetch('https://script.google.com/macros/s/AKfycbxAlLPXi0Knsl1xEUQGpyUgZ26oeVtlLlajiHHGc9sMHMjkg9WKZigqUvbRiunkcA1UwQ/exec?pattern=pattern2')
- You can retrieve the values from
'Subjects'!A1:B1000
and'Subjects_Classes'!A1:C1000
by the query parameter.
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)".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论