QUESTION: How do I insert text into cells of an existing table in Google slide?
let templatePres = SlidesApp.openById(templatePresId);
let slidesArr = templatePres.getSlides();
let templateSlideId = slidesArr[0].getObjectId();
let tblArr = slidesArr[0].getTables();
let theTblId = tblArr[0].getObjectId(); // table page element
let requests =[]
,numRows = 8, numCols = 8
,r, c, nextReq, nextVal
,theTableCellObj ,theTableRowObj ,theTableObj ,thePageElement
for ( c = 0 ; c < numCols ; c++ ) {
for ( r = 0 ; r < numRows ; r++ ) {
theTableCellObj =
{
"location": {
"rowIndex": r
,"columnIndex": c
},
"text": { nextVal }
};
theTableRowObj =
{
"tableCells": [ theTableCellObj ]
}
theTableObj =
{
"rows": numRows,
"columns": numCols,
"tableRows": [ theTableRowObj ]
}
thePageElement =
{
"objectId": theTblId,
"table": theTableObj
};
nextVal = inArr[r][c].toString();
console.log('r: ', r, ' c: ', c, ' val: ', nextVal );
nextReq =
{
"objectId": templateSlideId,
"pageType": 'SLIDE',
"pageElements": [thePageElement]
}
requests.push(nextReq);
console.log(JSON.stringify(nextReq) );
}
}
the error for every request in the batchUpdate looks like:
GoogleJsonResponseException: API call to slides.presentations.batchUpdate failed with error: Invalid JSON payload received. Unknown name "pageElements" at 'requests[0]': Cannot find field.
Invalid JSON payload received. Unknown name "pageType" at 'requests[0]': Cannot find field.
Invalid JSON payload received. Unknown name "objectId" at 'requests[0]': Cannot find field.
Invalid JSON payload received. Unknown name "pageElements" at 'requests[1]': Cannot find field.
Invalid JSON payload received. Unknown name "pageType" at 'requests[1]': Cannot find field.
...
This is a typical request displayed by console.log:
{
"objectId": "SLIDES_API142383606_0"
, "pageType": "SLIDE"
, "pageElements":
[
{
"objectId": "SLIDES_API142383606_1"
, "table":
{
"rows": 8
, "columns": 8
, "tableRows": [
{
"tableCells": [
{
"location": { "rowIndex": 3, "columnIndex": 2 }
,"text": { "nextVal": "silf" }
}
]
}
]
}
}
]
}
I used the documentation but I find it very difficult. Any help appreciated.
I added some logs to prove that getting the ids is not the problem.
try {
templatePres = SlidesApp.openById(templatePresId);
} catch ( err) {
ui.alert('You do not have a presentation. Ending.');
return;
}
try {
slidesArr = templatePres.getSlides();
} catch ( err) {
ui.alert('Could not get slides from presentation. Ending.');
return;
}
let templateSlideId = slidesArr[0].getObjectId();
console.log('templateSlideId: ', templateSlideId );
try {
tblArr = slidesArr[0].getTables();
} catch ( err) {
ui.alert('Could not get slides from presentation. Ending.');
return;
}
let theTblId = tblArr[0].getObjectId(); // table page element
console.log('ttheTblId: ', theTblId );
This is the log display:
templateSlideId: SLIDES_API1369298387_0
ttheTblId: SLIDES_API1369298387_1 // typo did not affect result
Previous attempt below did not work because I could not find a way to put in the slide id.
Step one: I make a copy in the existing presentation so that I can add text without messing up the original.
// https://www.debugcn.com/en/article/18027667.html
let slide0 = templatePres.getSlideById(templateSlideId)
// templatePres.insertSlide(insertionIndex, slide)
templatePres.insertSlide(0, slide0);
This temporary slide will be populated with text, downloaded as a png and inserted into the final presentation as background.
Step two: populate the text on the temporary slide w/o batch update:
for ( let r = 0 ; r < 9 ; r++ ) {
for ( let c = 0 ; c < 9 ; r++ ) {
theTbl.getCell(r, c). ??? there are no sets or inserts
}
}
The above will not work as there are no sets or inserts for text.
Step two: populate the text on the temporary slide with batch update:
Build batchUpdate request:
for ( let c = 0 ; c < 8 ; c++ ) {
for ( let r = 0 ; r < 8 ; r++ ) {
requests.push(
{"insertText":
{"objectId": theTblId
,"cellLocation": {
"rowIndex": r
,"columnIndex": c
}
,"text": inArr[r][c].toString()
// ,"text": inArr[r][c]
}
}
);
}
}
if (requests.length > 0) {
response = Slides.Presentations.batchUpdate(
{ 'requests': requests }, templatePresId );
}
It gets the error: GoogleJsonResponseException: API call to slides.presentations.batchUpdate failed with error: Invalid requests[0].insertText: The object (SLIDES_API1834905201_1) could not be found.
SLIDES_API1834905201_1 is the id of the existing slide with the mockup table. The above request contains the presentation id and the page element / table id but there is no place to enter the slide id???
I tried adding each of these requests separately into the request array before the insertText request as a way to identify the slide but obviously I do not understand the syntax in the documentation. The errors are listed.
Test 1 error: Unknown name "Page" at 'requests[0]': Cannot find field.
requests.push(
{'Page': {
"objectId": templateSlideId,
"pageType": 'SLIDE'
}}
);
Test 2: errors: Unknown name "pageType" at 'requests[0]': Cannot find field. Invalid JSON payload received. Unknown name "objectId" at 'requests[0]': Cannot find field.
requests.push(
{
"objectId": templateSlideId,
"pageType": 'SLIDE'
}
);
Test 3: error: Unknown name "SlideProperties" at 'requests[0]': Cannot find field.
requests.push(
{'SlideProperties': {
"layoutObjectId": templateSlideId
}}
);
THE QUESTION: Is it possible to insert text into an existing table in an existing slide?
I really do not want to use batch update to change all the dimensions and colors in my template / mockup table.
@Aerials posted Is there a way (or workaround) to add tables from Google Sheets to a Google Slides presentation using app script? Is there a way (or workaround) to add tables from Google Sheets to a Google Slides presentation using app script? He 1) created the slide, 2) added a table, 3) populated the table.
Another idea I looked at https://developers.google.com/slides/api/guides/styling#inserting_deleting_or_replacing_text Describes deleting text before inserting but if there is not text to delete?
I did run code to create slide with table followed by for loops to populate text runs and creates slide with populated table. It ran, created the slide with table but gave an error and the resulting slide was unsatisfactory.
let newSlideId = Utilities.getUuid();
requests.push(
{"createSlide": {
"objectId": newSlideId,
"insertionIndex": 0,
"slideLayoutReference": {
"predefinedLayout": "BLANK"
}
}
},
{ "createTable": {
"objectId": theTblId, // this id is in use on template slide but will be unique on this new slide
"elementProperties": {
"pageObjectId": newSlideId
},
"rows": 9,
"columns": 9
}
}
);
console.log('number of updates: ', requests.length);
if (requests.length > 0) {
response = Slides.Presentations.batchUpdate(
{ 'requests': requests }, templatePresId );
}
console.log('table created in new slide');
requests =[];
The table was surrounded by lots of white space. The column and row sizes are wrong and I would have to add the background colors. Batch update limits minimum row and column / widths and heights to 32 px which is too big. I am teaching and want the table to fill the slide so it can be seen. This is why I want to use an existing mockup table.
However, error: Service unavailable: Slides There is no additional code run after that shown above so no idea where this came from. Since I do not like the result - I do not care.
=================================== Begin response to Kos suggestion
The code:
function insertText(inArr) {
console.log('Begin insertText' );
console.log(inArr );
const ui = SpreadsheetApp.getUi();
let templatePres = SlidesApp.openById(templatePresId);
let slidesArr = templatePres.getSlides();
let templateSlideId = slidesArr[0].getObjectId();
console.log('templateSlideId: ', templateSlideId );
let tblArr = slidesArr[0].getTables();
let theTblId = tblArr[0].getObjectId(); // table page element
console.log('theTblId: ', theTblId );
let requests =[]
,numRows = 8, numCols = 8
,r, c, nextReq, nextVal
,theTableCellObj ,theTableRowObj ,theTableObj ,thePageElement
for ( let c = 0 ; c < 8 ; c++ ) {
for ( let r = 0 ; r < 8 ; r++ ) {
requests.push(
{"insertText":
{"objectId": theTblId
,"cellLocation": {
"rowIndex": r
,"columnIndex": c
}
,"text": inArr[r][c].toString()
// ,"text": inArr[r][c]
}
}
);
}
}
console.log('number of updates: ', requests.length);
if (requests.length > 0) {
response = Slides.Presentations.batchUpdate(
{ 'requests': requests }, templatePresId );
}
console.log('End insertText' );
}
The log:
Aug 4, 2021, 1:39:22 PM Debug ?slide=id.ge1649b52bc_0_0 slide[0] id: SLIDES_API2065850924_0
Aug 4, 2021, 1:39:23 PM Debug Begin insertText
Aug 4, 2021, 1:39:23 PM Debug [ [ ' ', ' ', 'A', 'B', 'C', 'D', 'E', 'F', ' ' ],
[ ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' ],
[ '1', ' ', 'zub', 'jip', 'kep', 'belm', 'frel', 'ras', ' ' ],
[ '2', ' ', 'drelf', 'bilp', 'bel', 'crift', 'posk', 'qualt', ' ' ],
[ '3', ' ', 'frusp', 'julm', 'hes', 'trin', 'taft', 'rop', ' ' ],
[ '4', ' ', 'sit', 'lit', 'trus', 'gub', 'buct', 'bloct', ' ' ],
[ '5', ' ', 'mef', 'dolp', 'hush', 'glap', 'crit', 'clack', ' ' ],
[ '6', ' ', 'crulp', 'gelt', 'quelm', 'dap', 'memp', 'rift', ' ' ],
[ ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' ] ]
Aug 4, 2021, 1:39:23 PM Debug templateSlideId: SLIDES_API1629452804_0
Aug 4, 2021, 1:39:23 PM Debug theTblId: SLIDES_API1629452804_1
Aug 4, 2021, 1:39:23 PM Debug number of updates: 64
Aug 4, 2021, 1:39:24 PM Error GoogleJsonResponseException: API call to slides.presentations.batchUpdate failed with error: Invalid requests[0].insertText: The object (SLIDES_API1629452804_1) could not be found.
at insertText(Code:243:37)
at makeMockup(Code:78:3)
At 1:39:23 PM Debug theTblId: SLIDES_API1629452804_1 the table was located and logged
At 1:39:24 PM Error GoogleJsonResponseException: API call to slides.presentations.batchUpdate failed with error: Invalid requests[0].insertText: The object (SLIDES_API1629452804_1) could not be found.
I am using the table id in the insertText command but it cannot be found. I assumed it was because the slide id was not input and therefore tried the more complex batchUpdate which also does not work.
End response to Kos
HISTORY - I want to create a table from a list in a spreadsheet which will fill the background of a Google slide or Jamboard.
Method 1. Create a table in the same spreadsheet as the list and use in teaching. Problem: Has all the toolbars for spreadsheets eating up space on small student devices.
Method 2. NOT SUPPORTED: Extract the table (range of cells in spreadsheet) as an image and load to slide background. I cannot find any functions in Google Spreadsheets to do this. It may be possible in Excel?
Method 3. Create a table in the same spreadsheet as your list, Read 'some' of the characteristics of the table in the spreadsheet. Create a slide, insert a table and format it with the limited data available from the characteristics of the table in the spreadsheet (including text). Download the table from the slide as an image and upload it as a background image. The resulting table (for a 9x9) was bigger than the slide. When the image was loaded to a slide, the table was surrounded by useless white space and mostly unformatted. "https://stackoverflow.com/questions/58327686/get-range-of-google-spreadsheet-into-an-image-using-google-script/58336922"
Method 4. NOT SUPPORTED: Create an array from the first 36 items of data. Manually create a slide with a table and format it with the limited attributes possible. Use simple update table on slide without batch update. theTbl.getCell has not set or insert methods.
Method 5. GETS ERRORS: Create an array from the first 36 items of data. Manually create a slide with a table and format it with the limited attributes possible. Use simple batch update insertText to populate slide table with text from the array. Download the table as an image and upload it as a background image. This is different from method three as the table on the slide is existing. This is the method @Kos discussed in his 'answer'. See errors above (search for Kos)
Method 6. GETS ERRORS: Create an array from the data. Create a slide copy of a mockup that has beautiful styling and text attributes. Use complex batch update to populate slide table with text from the array. Download as an image and save as background in a new slide in a new or existing presentation. Delete all temporary stuff. See errors above (top example)
First, it won't work because you are calling the wrong slide! :) When you duplicate the slide, you now have to fill in the second slide, not the first.
function populateMockupTable(inArr) {
console.log('Begin populateMockupTable' );
// console.log(inArr);
const ui = SpreadsheetApp.getUi();
let templatePres, slidesArr, tblArr;
try {
templatePres = SlidesApp.openById(templatePresId);
} catch ( err) {
ui.alert('You do not have a presentation. Ending.');
return;
}
try {
slidesArr = templatePres.getSlides();
} catch ( err) {
ui.alert('Could not get slides from presentation. Ending.');
return;
}
let templateSlideId = slidesArr[1].getObjectId();
// console.log('templateSlideId: ', templateSlideId );
try {
tblArr = ***slidesArr[1]***.getTables();
} catch ( err) {
ui.alert('Could not get slides from presentation. Ending.');
return;
}
let theTblId = tblArr[0].getObjectId(); // table page element
// console.log('theTblId: ', theTblId );
let requests =[], numRows = inArr.length, numCols = inArr[0].length, r, c;
for ( let r = 0 ; r < numRows ; r++ ) {
for ( let c = 0 ; c < numCols ; c++ ) {
requests.push(
{"insertText":
{"objectId": theTblId
,"cellLocation": {
"rowIndex": r,
"columnIndex": c
}
,"text": inArr[r][c].toString()
}
}
);
}
}
// console.log('number of updates: ', requests.length);
if (requests.length > 0) {
var response = Slides.Presentations.batchUpdate(
{ 'requests': requests }, templatePresId );
}
// console.log('End populateMockupTable' );
// console.log(requests);
}