dear all ,
I have uploaded the text files and there is no problem using the app script.
However, I am not able to receive the data from Google Sheets. The data needs to be pulled from Google Sheet and plotted.
please help me to rectify this issues.
when i remove the block for lisf csv talbe text for error >> html error "
Test (1).aia (2.6 KB)
appscript.txt (2.8 KB)
Take a look here. You have examples abut how to retrieve and delete data from googleSheet (blocks and script):
You are getting an error from this section in your script:
// Create text output with specified character encoding (UTF-8 in this case)
var output = ContentService.createTextOutput(jsonData)
.setMimeType(ContentService.MimeType.JSON)
.setContent(jsonData)
.setCharset('UTF-8');
It is usually not necessary to do this, so change your script to:
// Create text output with specified character encoding (UTF-8 in this case)
var output = ContentService.createTextOutput(jsonData);
and see what happens...
you are missing a semi-colon
You also may need to change the other stanza that does the same thing for an error message.
Did you correctly deploy a new version of your script ?
after done new version of the script then its working.
What type of chart do you want to display. Show an example.
You want three separate charts or one combined chart ?
combined chart.
Your delete sheet does not work because it is in a separate function, not in the doGet(e). You will need to combine it like this:
function doGet(e) {
if (e.parameter.fn == "getData" ) {
try {
// Open the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet named 'Sheet1'
var sheet = ss.getSheetByName('Sheet1');
// Check if the sheet exists
if (!sheet) {
throw new Error("Sheet 'Sheet1' not found.");
}
// Get all data in the sheet
var data = sheet.getDataRange().getDisplayValues();
// Convert the data to JSON
var jsonData = JSON.stringify(data);
// Create text output with specified character encoding (UTF-8 in this case)
var output = ContentService.createTextOutput(jsonData)
.setMimeType(ContentService.MimeType.JSON)
.setContent(jsonData)
.setCharset('UTF-8');
return output;
} catch (error) {
// Return error message as JSON
var errorMessage = JSON.stringify({ error: error.message });
// Create text output for the error message with specified character encoding
var errorOutput = ContentService.createTextOutput(errorMessage)
.setMimeType(ContentService.MimeType.JSON)
.setContent(errorMessage)
.setCharset('UTF-8');
return errorOutput;
}
} else if (e.parameter.fn == "Delete") {
try {
// Open the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet named 'Sheet1'
var sheet = ss.getSheetByName('Sheet1');
// Check if the sheet exists
if (!sheet) {
throw new Error("Sheet 'Sheet1' not found.");
}
// Clear the contents of the sheet
sheet.clearContents();
console.log('Sheet cleared successfully.');
} catch (error) {
console.error('Error:', error.message);
}
}
}
and then adjust your blocks to send the parameter fn with the required value
first of all thank you for your feedback .
i tryed your new scrept bot not get the result .
Did you deploy a new version ?
What response do you get from the script ?
Yes deploy new version..
Same error [doc typehtml ]
Show the complete error
In frustration, I built up my own setup, which works:
BLOCKS
Note the use of ? for the first parameter. Subsequent parameters would use a &.
SCRIPT
function doPost(e) {
try {
// Open the spreadsheet by URL
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet named "Sheet1"
var sh = ss.getSheetByName("Sheet1");
if (!sh) {
throw new Error('Sheet "Sheet1" not found');
}
// Get the contents of the POST request
var data = JSON.parse(e.postData.contents);
// Check if data is an array or array of arrays
if (Array.isArray(data)) {
if (!Array.isArray(data[0])) {
// If data is a single array, convert it to a 2D array
data = [data];
}
// Append each row to the sheet
sh.getRange(sh.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
} else {
throw new Error('Invalid data format: Expected an array or array of arrays.');
}
// Return a success response
return ContentService.createTextOutput("Success");
} catch (error) {
// Return an error response
return ContentService.createTextOutput('Error: ' + error.message);
}
}
function doGet(e) {
if (e.parameter.fn == "getData" ) {
try {
// Open the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet named 'Sheet1'
var sheet = ss.getSheetByName('Sheet1');
// Check if the sheet exists
if (!sheet) {
throw new Error("Sheet 'Sheet1' not found.");
}
// Get all data in the sheet
var data = sheet.getDataRange().getDisplayValues();
// Convert the data to JSON
var jsonData = JSON.stringify(data);
// Create text output with specified character encoding (UTF-8 in this case)
var output = ContentService.createTextOutput(jsonData);
return output;
} catch (error) {
// Return error message as JSON
var errorMessage = JSON.stringify({ error: error.message });
// Create text output for the error message with specified character encoding
var errorOutput = ContentService.createTextOutput(errorMessage);
return errorOutput;
}
} else if (e.parameter.fn == "Delete") {
try {
// Open the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the sheet named 'Sheet1'
var sheet = ss.getSheetByName('Sheet1');
// Check if the sheet exists
if (!sheet) {
throw new Error("Sheet 'Sheet1' not found.");
} else{
// Clear the contents of the sheet
sheet.clearContents();
return ContentService.createTextOutput('Sheet cleared successfully.');
}
} catch (error) {
return ContentService.createTextOutput('Error:', error.message);
}
}
}
thank you for feed back now its working ..
one help required. for dispay the each value in each labe full row y1, y2, y3, and x value displayed on seperate label.
Do you mean a header row for your x values ?