This is my first time posting.
I'd like to create a file for an E-quote with a spreadsheet and automatically enter the value of a particular cell in the file into a separate spreadsheet sales file.I would like to reflect other members in the same sales file, so I would like to avoid overlapping cells. Is there any way?
I would like to create a button in the E-quote file so that it can be executed by pressing the button.
I'm a beginner, so I don't know where to start
Could you please let me know?Thank you for your cooperation
google-apps-script google-spreadsheet
Hello, nice to meet you
If so, I think it would be better to aggregate it in Google-Apps-Script.
Below is a script that summarizes the contents of any "Sheet" in "Output" on one sheet.
Add the following script from the Script Editor of the Tool.
Use your spreadSheetKey to create your own
"If you change the sheet to the one on the sheet and create a sheet under the names ""Sheet"" and ""Output"", it will work."
If you don't understand it at all, please start with the tutorial.
https://developers.google.com/apps-script/overview#your_first_script
function toSummary(){
varsMitumori=SpreadsheetApp.getActiveSpreadsheet();
varmitumoriId=ssMitumori.getId();
varsheetRef=ssMitumori.getSheetByName("Sheet");
// Copy the data you want to move, use the ID of the sheet as the key to prevent duplication
vardata={"ID":mitumoriId";
data["A1"] = sheetRef.getRange("A1").getValue();
// Now open the sheet for the summary: Specify the sheet ID (long random string) in url //
// The spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is "abc1234567".
var spreadsheetKey="[DUMMY]";
varsSyuukei=SpreadsheetApp.openById(spreadsheetKey);
varsheetRef=ssSyuukei.getSheetByName("Output");
// A = ID, B = value shall be retained
// sheetRef.getLastColumn
varoldDatas=sheetRef.getRange("A1:B").getValues(); // Retrieve and retain data stored in columns A, B
varfiltered=oldDatas.filter(function(r){
return r.join("").length>0;
});
varmerge={};
for (vark filtered) {
var v = filtered [k];
merge[v[0]]={"ID":v[0], "A1":v[1]}
}
merge [data.ID] = data;
sheetRef.clear(); // I have secured the original data in the array, so I will delete the sheet once.
// Output one line at a time on the aggregation destination sheet
varcolNo=1;
varrowRef={0:"ID", 1:"A1"}
for (vark in merge) {
var column = merge [k];
j = 1;
for(var_key in rowRef){
varkey=rowRef[_key];
sheetRef.getRange(colNo,j).setValue(column[key]);
j = j+1;
}
colNo++;
}
}
If you want to add a value, you can increase it as follows.
function toSummary(){
varsMitumori=SpreadsheetApp.getActiveSpreadsheet();
varmitumoriId=ssMitumori.getId();
varsheetRef=ssMitumori.getSheetByName("Sheet");
// Copy the data you want to move on the sheet side of the aggregation source, and use the ID of the sheet as the key to prevent duplication
vardata={"ID":mitumoriId";
data["A1"] = sheetRef.getRange("A1").getValue();
data["B1"] = ssMitumori.getUrl() // Value you want to add
// Now open the sheet for the summary: Specify the sheet ID (long random string) in url //
// The spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is "abc1234567".
var spreadsheetKey="[DUMMY]";
varsSyuukei=SpreadsheetApp.openById(spreadsheetKey);
varsheetRef=ssSyuukei.getSheetByName("Output");
// A = ID, B = value from sheet, C = URL shall be retained
varoldDatas=sheetRef.getRange("A1:C").getValues(); // Retrieve and retain data stored in columns A, B, and C
varfiltered=oldDatas.filter(function(r){
return r.join("").length>0;
});
varmerge={};
for (vark filtered) {
var v = filtered [k];
merge[v[0]]={"ID":v[0], "A1":v[1], "B1":v[2]}
}
merge [data.ID] = data;
// Output the contents of `merge` one line at a time on the destination sheet.
sheetRef.clear(); // I have secured the original data in the array, so I will delete the aggregated sheet once.
varcolNo=1;
varrowRef={0:"ID", 1:"A1", 2:"B1"}
for (vark in merge) {
var column = merge [k];
j = 1;
for(var_key in rowRef){
varkey=rowRef[_key];
sheetRef.getRange(colNo,j).setValue(column[key]);
j = j+1;
}
colNo++;
}
}
© 2024 OneMinuteCode. All rights reserved.